<!DOCTYPE html><html lang="en" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>MySQL学习笔记 | Sobremesa的个人空间</title><meta name="keywords" content="MySql"><meta name="author" content="Li YunBO"><meta name="copyright" content="Li YunBO"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><meta name="description" content="MySQL学习笔记登录和退出MySQL服务器12345# 登录MySQL$ mysql -u root -p12345612# 退出MySQL数据库服务器exit;  基本语法123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354-- 显示所有数据库sho">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL学习笔记">
<meta property="og:url" content="http://example.com/2021/04/17/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/index.html">
<meta property="og:site_name" content="Sobremesa的个人空间">
<meta property="og:description" content="MySQL学习笔记登录和退出MySQL服务器12345# 登录MySQL$ mysql -u root -p12345612# 退出MySQL数据库服务器exit;  基本语法123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354-- 显示所有数据库sho">
<meta property="og:locale" content="en_US">
<meta property="og:image" content="http://example.com/img/%E5%A4%B4%E5%83%8F2.jpg">
<meta property="article:published_time" content="2021-04-17T13:29:32.000Z">
<meta property="article:modified_time" content="2021-08-09T12:50:38.998Z">
<meta property="article:author" content="Li YunBO">
<meta property="article:tag" content="MySql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://example.com/img/%E5%A4%B4%E5%83%8F2.jpg"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="http://example.com/2021/04/17/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css"><script>var GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true},
  copy: {
    success: 'Copy successfully',
    error: 'Copy error',
    noSupport: 'The browser does not support'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: 'Just',
    min: 'minutes ago',
    hour: 'hours ago',
    day: 'days ago',
    month: 'months ago'
  },
  copyright: undefined,
  ClickShowText: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: false
};

var saveToLocal = {
  set: function setWithExpiry(key, value, ttl) {
    const now = new Date()
    const expiryDay = ttl * 86400000
    const item = {
      value: value,
      expiry: now.getTime() + expiryDay,
    }
    localStorage.setItem(key, JSON.stringify(item))
  },

  get: function getWithExpiry(key) {
    const itemStr = localStorage.getItem(key)

    if (!itemStr) {
      return undefined
    }
    const item = JSON.parse(itemStr)
    const now = new Date()

    if (now.getTime() > item.expiry) {
      localStorage.removeItem(key)
      return undefined
    }
    return item.value
  }
}

// https://stackoverflow.com/questions/16839698/jquery-getscript-alternative-in-native-javascript
const getScript = url => new Promise((resolve, reject) => {
  const script = document.createElement('script')
  script.src = url
  script.async = true
  script.onerror = reject
  script.onload = script.onreadystatechange = function() {
    const loadState = this.readyState
    if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
    script.onload = script.onreadystatechange = null
    resolve()
  }
  document.head.appendChild(script)
})</script><script id="config_change">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-08-09 20:50:38'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(function () {  window.activateDarkMode = function () {
    document.documentElement.setAttribute('data-theme', 'dark')
    if (document.querySelector('meta[name="theme-color"]') !== null) {
      document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
    }
  }
  window.activateLightMode = function () {
    document.documentElement.setAttribute('data-theme', 'light')
   if (document.querySelector('meta[name="theme-color"]') !== null) {
      document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
    }
  }
  const autoChangeMode = 'false'
  const t = saveToLocal.get('theme')
  if (autoChangeMode === '1') {
    const isDarkMode = window.matchMedia('(prefers-color-scheme: dark)').matches
    const isLightMode = window.matchMedia('(prefers-color-scheme: light)').matches
    const isNotSpecified = window.matchMedia('(prefers-color-scheme: no-preference)').matches
    const hasNoSupport = !isDarkMode && !isLightMode && !isNotSpecified
    if (t === undefined) {
      if (isLightMode) activateLightMode()
      else if (isDarkMode) activateDarkMode()
      else if (isNotSpecified || hasNoSupport) {
        const now = new Date()
        const hour = now.getHours()
        const isNight = hour <= 6 || hour >= 18
        isNight ? activateDarkMode() : activateLightMode()
      }
      window.matchMedia('(prefers-color-scheme: dark)').addListener(function (e) {
        if (saveToLocal.get('theme') === undefined) {
          e.matches ? activateDarkMode() : activateLightMode()
        }
      })
    } else if (t === 'light') activateLightMode()
    else activateDarkMode()
  } else if (autoChangeMode === '2') {
    const now = new Date()
    const hour = now.getHours()
    const isNight = hour <= 6 || hour >= 18
    if (t === undefined) isNight ? activateDarkMode() : activateLightMode()
    else if (t === 'light') activateLightMode()
    else activateDarkMode()
  } else {
    if (t === 'dark') activateDarkMode()
    else if (t === 'light') activateLightMode()
  }const asideStatus = saveToLocal.get('aside-status')
if (asideStatus !== undefined) {
   if (asideStatus === 'hide') {
     document.documentElement.classList.add('hide-aside')
   } else {
     document.documentElement.classList.remove('hide-aside')
   }
}})()</script><meta name="generator" content="Hexo 5.4.0"><link rel="alternate" href="/atom.xml" title="Sobremesa的个人空间" type="application/atom+xml">
</head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="/img/%E5%A4%B4%E5%83%8F2.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">Articles</div><div class="length-num">193</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">Tags</div><div class="length-num">25</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">Categories</div><div class="length-num">18</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 档案</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 娱乐</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/music/"><i class="fa-fw fas fa-music"></i><span> Music</span></a></li><li><a class="site-page" href="/movies/"><i class="fa-fw fas fa-video"></i><span> Movie</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div id="body-wrap"><header class="post-bg" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">Sobremesa的个人空间</a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 档案</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 娱乐</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/music/"><i class="fa-fw fas fa-music"></i><span> Music</span></a></li><li><a class="site-page" href="/movies/"><i class="fa-fw fas fa-video"></i><span> Movie</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">MySQL学习笔记</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">Created</span><time class="post-meta-date-created" datetime="2021-04-17T13:29:32.000Z" title="Created 2021-04-17 21:29:32">2021-04-17</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">Updated</span><time class="post-meta-date-updated" datetime="2021-08-09T12:50:38.998Z" title="Updated 2021-08-09 20:50:38">2021-08-09</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/MySql/">MySql</a></span></div><div class="meta-secondline"> <span class="post-meta-separator">|</span><span class="post-meta-pv-cv"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">Post View:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="MySQL学习笔记"><a href="#MySQL学习笔记" class="headerlink" title="MySQL学习笔记"></a>MySQL学习笔记</h1><h2 id="登录和退出MySQL服务器"><a href="#登录和退出MySQL服务器" class="headerlink" title="登录和退出MySQL服务器"></a>登录和退出MySQL服务器</h2><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta">#</span><span class="bash"> 登录MySQL</span><br><span class="hljs-meta">$</span><span class="bash"> mysql -u root -p12345612</span><br><br><span class="hljs-meta">#</span><span class="bash"> 退出MySQL数据库服务器</span><br>exit;<br></code></pre></td></tr></table></figure>

<h2 id="基本语法"><a href="#基本语法" class="headerlink" title="基本语法"></a>基本语法</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 显示所有数据库<br>show databases;<br><br>-- 创建数据库<br>CREATE DATABASE test;<br><br>-- 切换数据库<br>use test;<br><br>-- 显示数据库中的所有表<br>show tables;<br><br>-- 创建数据表<br>CREATE TABLE pet (<br>    name VARCHAR(20),<br>    owner VARCHAR(20),<br>    species VARCHAR(20),<br>    sex CHAR(1),<br>    birth DATE,<br>    death DATE<br>);<br><br>-- 查看数据表结构<br>-- describe pet;<br>desc pet;<br><br>-- 查询表<br>SELECT * from pet;<br><br>-- 插入数据<br>INSERT INTO pet VALUES (&#39;puffball&#39;, &#39;Diane&#39;, &#39;hamster&#39;, &#39;f&#39;, &#39;1990-03-30&#39;, NULL);<br><br>-- 修改数据<br>UPDATE pet SET name &#x3D; &#39;squirrel&#39; where owner &#x3D; &#39;Diane&#39;;<br><br>-- 删除数据<br>DELETE FROM pet where name &#x3D; &#39;squirrel&#39;;<br><br>-- 修改数据<br>updata pet set name &#x3D; &#39;旺旺茶&#39; where owner&#x3D;&#39;周星驰&#39;<br><br>-- 删除表<br>DROP TABLE myorder;<br><br>总结一下：数据记录常见的操作<br>--增加<br>INSERT<br>--删除<br>DELETE<br>--修改<br>UPDATA<br>--查询<br>SELECT<br><br></code></pre></td></tr></table></figure>

<h2 id="mysql建表约束"><a href="#mysql建表约束" class="headerlink" title="mysql建表约束"></a>mysql建表约束</h2><h3 id="1、主键约束"><a href="#1、主键约束" class="headerlink" title="1、主键约束"></a>1、主键约束</h3><pre><code>它能够唯一确定一张表中的一条记录，也就是通过某个字段添加约束，是的该字段不重复且不为空
</code></pre>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 主键约束<br>-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。<br>CREATE TABLE user (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20)<br>);<br>第一次插入 insert into user values(1,&#39;张三&#39;);成功插入<br>第二次插入 insert into user values(1,&#39;张三&#39;);插入失败，不可重复<br>插入       insert into user values(2,&#39;张三&#39;); 成功，id不相同<br>		  insert into user values(NULL,&#39;张三&#39;);错误，id不能为空<br><br>-- 联合主键<br>-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。<br>CREATE TABLE user2 (<br>    id INT,<br>    name VARCHAR(20),<br>    password VARCHAR(20),<br>    PRIMARY KEY(id, name)<br>);<br>插入 insert into user2 values(1,&#39;张三&#39;,&#39;123&#39;);成功<br>插入 insert into user2 values(2,&#39;张三&#39;,&#39;123&#39;);成功<br>插入 insert into user2 values(1,&#39;李四&#39;,&#39;123&#39;);成<br>插入 insert into user2 values(NULL,&#39;李四&#39;,&#39;123&#39;);失败。依旧不能为空<br><br><br>-- 自增约束<br>-- 自增约束的主键由系统自动递增分配。<br>CREATE TABLE user3 (<br>    id INT PRIMARY KEY AUTO_INCREMENT,<br>    name VARCHAR(20)<br>);<br>insert into user3 (name) values(&#39;zhangsan&#39;);  &#x2F;&#x2F;&#x2F;没有写ID，但是会自己添加id id&#x3D;1<br>insert into user3 (name) values(&#39;zhangsan&#39;);  &#x2F;&#x2F;&#x2F;没有写ID，但是会自己添加id id&#x3D;2<br><br>--如果说我们创建表的时候，忘记创建主键约束，该怎么办？<br><br>create table user4(<br>	id INT,<br>	name varchar(20)<br>);<br>可以通过下面的方式去添加主键<br>-- 添加主键约束<br>ALTER TABLE user4 ADD PRIMARY KEY(id);<br>或者通过modify 修改字段，添加约束<br>ALTER TABLE user4 MODIFY id INT PRIMARY KEY;<br><br>-- 删除主键<br>ALTER TABLE user4 drop PRIMARY KEY;<br></code></pre></td></tr></table></figure>

<h3 id="3、唯一主键"><a href="#3、唯一主键" class="headerlink" title="3、唯一主键"></a>3、唯一主键</h3><p>–约束修饰的字段的值不可以重复</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 建表时创建唯一主键<br>CREATE TABLE user5 (<br>    id INT,<br>    name VARCHAR(20),<br>    UNIQUE(name)<br>);<br>或者换个写法：<br>CREATE TABLE user6 (<br>    id INT,<br>    name VARCHAR(20) UNIQUE<br>);<br><br>CREATE TABLE user5 (<br>    id INT,<br>    name VARCHAR(20),<br>    UNIQUE(id,name)                &#x2F;&#x2F;这种要求id和name不能同时相同。可以id不同，name相同<br>);<br><br>-- 添加唯一主键<br>-- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：<br>ALTER TABLE user5 ADD UNIQUE(name);<br>ALTER TABLE user5 MODIFY name VARCHAR(20) UNIQUE;<br><br>第一次 insert into user2 values(1,&#39;张三&#39;,&#39;123&#39;); 成功<br>第二次 insert into user2 values(1,&#39;张三&#39;,&#39;123&#39;); 失败，不可重复添加相同的name<br><br>-- 删除唯一主键<br>ALTER TABLE user7 DROP INDEX name;<br></code></pre></td></tr></table></figure>

<p>–总结：<br>–1、见表的时候就添加约束<br>–2、可以使用alter。。。add。。。<br>–3、alter。。。modif。。。<br>–4、删除alter。。。drop。。。</p>
<p>###4、 非空约束<br>–修饰的字段不能为空NULL</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 建表时添加非空约束<br>-- 约束某个字段不能为空<br>CREATE TABLE user9 (<br>    id INT,<br>    name VARCHAR(20) NOT NULL<br>);<br><br><br>insert into user8 values(1); &#x2F;&#x2F;报错，只穿了id，没有name，那么不能为空<br>insert into user8 values(1,&#39;张三&#39;);&#x2F;&#x2F;成功<br>insert into user8 values(&#39;lisi&#39;); &#x2F;&#x2F;成功，因为id可以为空<br><br>-- 移除非空约束<br>ALTER TABLE user MODIFY name VARCHAR(20);<br></code></pre></td></tr></table></figure>

<h3 id="5、默认约束"><a href="#5、默认约束" class="headerlink" title="5、默认约束"></a>5、默认约束</h3><p>–就是当我们插入字段的时候，如果没有传值，就会使用默认值</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 建表时添加默认约束<br>-- 约束某个字段的默认值<br>CREATE TABLE user10 (<br>    id INT,<br>    name VARCHAR(20),<br>    age INT DEFAULT 10<br>);<br><br>insert into user10 (id,name) values(1,&#39;zhangsan&#39;)&#x2F;&#x2F;age会默认为10；<br>-- 移除非空约束<br>ALTER TABLE user MODIFY age INT;<br></code></pre></td></tr></table></figure>

<h3 id="外键约束"><a href="#外键约束" class="headerlink" title="外键约束"></a>外键约束</h3><p>–涉及到两个表：父表，子表<br>–主表，副表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 班级<br>CREATE TABLE classes (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20)<br>);<br><br>-- 学生表<br>CREATE TABLE students (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20),<br>    -- 这里的 class_id 要和 classes 中的 id 字段相关联<br>    class_id INT,<br>    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值<br>    FOREIGN KEY(class_id) REFERENCES classes(id)<br>);<br><br><br>insert into classes values(1,&#39;一班&#39;);<br>insert into classes values(2,&#39;二班&#39;);<br>insert into classes values(3,&#39;三班&#39;);<br>insert into classes values(4,&#39;四班&#39;);<br><br>insert into students values(&#39;1001&#39;,&#39;张三&#39;,1);<br>insert into students values(&#39;1002&#39;,&#39;张三&#39;,2);<br>insert into students values(&#39;1003&#39;,&#39;张三&#39;,3);<br>insert into students values(&#39;1004&#39;,&#39;张三&#39;,4);<br><br><br>-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；<br>-- 2. 主表中的记录被副表引用时，主表不可以被删除。<br>delete from classes where id&#x3D;4;报错，<br></code></pre></td></tr></table></figure>




<h2 id="数据库的三大设计范式"><a href="#数据库的三大设计范式" class="headerlink" title="数据库的三大设计范式"></a>数据库的三大设计范式</h2><h3 id="1NF-第一范式"><a href="#1NF-第一范式" class="headerlink" title="1NF 第一范式"></a>1NF 第一范式</h3><p>数据表中的所有字段都是不可分割的原子值</p>
<p>create table student2(<br>    id int primary key,<br>    name varchar(20),<br>    address varchar(30)<br>);</p>
<p>insert into student2 values(1,’颤三’,’中国四川成都武侯区武侯大道100号’);<br>insert into student2 values(2,’里斯’,’中国四川成都武侯区京城大道200号’);<br>insert into student2 values(3,’王武’,’中国四川成都高新区高新大道90号’);</p>
<hr>
<p>–只要字段值还可以继续拆分，就不满足第一范式。<br>这里address可以继续拆分为细小的地址<br>address-&gt;country | privence | city| details 所以不满足第一范式</p>
<p>create table student3(<br>    id int primary key,<br>    name varchar(20),<br>    country varchar(30),<br>    privence varchar(30),<br>    city varchar(30),<br>    details varchar(30)<br>);</p>
<p>insert into student3 values(1,’颤三’,’中国’,’四川’,’成都’,’武侯区武侯大道100号’);<br>insert into student3 values(2,’里斯’,’中国’,’四川’,’成都’,’武侯区京城大道200号’);<br>insert into student3 values(3,’王武’,’中国’,’四川’,’成都’,’高新区高新大道90号’);</p>
<hr>
<p>范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。</p>
<h3 id="2NF-第二范式"><a href="#2NF-第二范式" class="headerlink" title="2NF 第二范式"></a>2NF 第二范式</h3><p>在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 订单表<br>CREATE TABLE myorder (<br>    product_id INT,<br>    customer_id INT,<br>    product_name VARCHAR(20),<br>    customer_name VARCHAR(20),<br>    PRIMARY KEY (product_id, customer_id)<br>);<br></code></pre></td></tr></table></figure>
<p>问题？<br>–除主键以外的其他列，只依赖与主键的部分字段</p>
<p>实际上，在这张订单表中，<code>product_name</code> 只依赖于 <code>product_id</code> ，<code>customer_name</code> 只依赖于 <code>customer_id</code> 。也就是说，<code>product_name</code> 和 <code>customer_id</code> 是没用关系的，<code>customer_name</code> 和 <code>product_id</code> 也是没有关系的。</p>
<p>这就不满足第二范式：其他列都必须完全依赖于主键列！</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><code class="hljs mysql"><br>CREATE TABLE myorder (<br>    order_id INT PRIMARY KEY,<br>    product_id INT,<br>    customer_id INT<br>);<br>&#x2F;&#x2F;产品表<br>CREATE TABLE product (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20)<br>);<br>&#x2F;&#x2F;消费者表<br>CREATE TABLE customer (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20)<br>);<br></code></pre></td></tr></table></figure>

<p>拆分之后，<code>myorder</code> 表中的 <code>product_id</code> 和 <code>customer_id</code> 完全依赖于 <code>order_id</code> 主键，而 <code>product</code> 和 <code>customer</code> 表中的其他字段又完全依赖于主键。满足了第二范式的设计！</p>
<h3 id="3NF"><a href="#3NF" class="headerlink" title="3NF"></a>3NF</h3><p>在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs mysql">CREATE TABLE myorder (<br>    order_id INT PRIMARY KEY,<br>    product_id INT,<br>    customer_id INT,<br>    customer_phone VARCHAR(15)<br>);<br></code></pre></td></tr></table></figure>

<p>表中的 <code>customer_phone</code> 有可能依赖于 <code>order_id</code> 、 <code>customer_id</code> 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs mysql">CREATE TABLE myorder (<br>    order_id INT PRIMARY KEY,<br>    product_id INT,<br>    customer_id INT<br>);<br><br>CREATE TABLE customer (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20),<br>    phone VARCHAR(15)<br>);<br></code></pre></td></tr></table></figure>

<p>修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！</p>
<h2 id="查询练习"><a href="#查询练习" class="headerlink" title="查询练习"></a>查询练习</h2><h3 id="准备数据"><a href="#准备数据" class="headerlink" title="准备数据"></a>准备数据</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 创建数据库<br>CREATE DATABASE select_test;<br>-- 切换数据库<br>USE select_test;<br><br>-- 创建学生表<br>CREATE TABLE student (<br>    no VARCHAR(20) PRIMARY KEY,<br>    name VARCHAR(20) NOT NULL,<br>    sex VARCHAR(10) NOT NULL,<br>    birthday DATE, -- 生日<br>    class VARCHAR(20) -- 所在班级<br>);<br><br>-- 创建教师表<br>CREATE TABLE teacher (<br>    no VARCHAR(20) PRIMARY KEY,<br>    name VARCHAR(20) NOT NULL,<br>    sex VARCHAR(10) NOT NULL,<br>    birthday DATE,<br>    profession VARCHAR(20) NOT NULL, -- 职称<br>    department VARCHAR(20) NOT NULL -- 部门<br>);<br><br>-- 创建课程表<br>CREATE TABLE course (<br>    no VARCHAR(20) PRIMARY KEY,<br>    name VARCHAR(20) NOT NULL,<br>    t_no VARCHAR(20) NOT NULL, -- 教师编号<br>    -- 表示该 tno 来自于 teacher 表中的 no 字段值<br>    FOREIGN KEY(t_no) REFERENCES teacher(no) <br>);<br><br>-- 成绩表<br>CREATE TABLE score (<br>    s_no VARCHAR(20) NOT NULL, -- 学生编号<br>    c_no VARCHAR(20) NOT NULL, -- 课程号<br>    degree DECIMAL,	-- 成绩<br>    -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值<br>    FOREIGN KEY(s_no) REFERENCES student(no),	<br>    FOREIGN KEY(c_no) REFERENCES course(no),<br>    -- 设置 s_no, c_no 为联合主键<br>    PRIMARY KEY(s_no, c_no)<br>);<br><br>-- 查看所有表<br>SHOW TABLES;<br><br>-- 添加学生表数据<br>INSERT INTO student VALUES(&#39;101&#39;, &#39;曾华&#39;, &#39;男&#39;, &#39;1977-09-01&#39;, &#39;95033&#39;);<br>INSERT INTO student VALUES(&#39;102&#39;, &#39;匡明&#39;, &#39;男&#39;, &#39;1975-10-02&#39;, &#39;95031&#39;);<br>INSERT INTO student VALUES(&#39;103&#39;, &#39;王丽&#39;, &#39;女&#39;, &#39;1976-01-23&#39;, &#39;95033&#39;);<br>INSERT INTO student VALUES(&#39;104&#39;, &#39;李军&#39;, &#39;男&#39;, &#39;1976-02-20&#39;, &#39;95033&#39;);<br>INSERT INTO student VALUES(&#39;105&#39;, &#39;王芳&#39;, &#39;女&#39;, &#39;1975-02-10&#39;, &#39;95031&#39;);<br>INSERT INTO student VALUES(&#39;106&#39;, &#39;陆军&#39;, &#39;男&#39;, &#39;1974-06-03&#39;, &#39;95031&#39;);<br>INSERT INTO student VALUES(&#39;107&#39;, &#39;王尼玛&#39;, &#39;男&#39;, &#39;1976-02-20&#39;, &#39;95033&#39;);<br>INSERT INTO student VALUES(&#39;108&#39;, &#39;张全蛋&#39;, &#39;男&#39;, &#39;1975-02-10&#39;, &#39;95031&#39;);<br>INSERT INTO student VALUES(&#39;109&#39;, &#39;赵铁柱&#39;, &#39;男&#39;, &#39;1974-06-03&#39;, &#39;95031&#39;);<br><br>-- 添加教师表数据<br>INSERT INTO teacher VALUES(&#39;804&#39;, &#39;李诚&#39;, &#39;男&#39;, &#39;1958-12-02&#39;, &#39;副教授&#39;, &#39;计算机系&#39;);<br>INSERT INTO teacher VALUES(&#39;856&#39;, &#39;张旭&#39;, &#39;男&#39;, &#39;1969-03-12&#39;, &#39;讲师&#39;, &#39;电子工程系&#39;);<br>INSERT INTO teacher VALUES(&#39;825&#39;, &#39;王萍&#39;, &#39;女&#39;, &#39;1972-05-05&#39;, &#39;助教&#39;, &#39;计算机系&#39;);<br>INSERT INTO teacher VALUES(&#39;831&#39;, &#39;刘冰&#39;, &#39;女&#39;, &#39;1977-08-14&#39;, &#39;助教&#39;, &#39;电子工程系&#39;);<br><br>-- 添加课程表数据<br>INSERT INTO course VALUES(&#39;3-105&#39;, &#39;计算机导论&#39;, &#39;825&#39;);<br>INSERT INTO course VALUES(&#39;3-245&#39;, &#39;操作系统&#39;, &#39;804&#39;);<br>INSERT INTO course VALUES(&#39;6-166&#39;, &#39;数字电路&#39;, &#39;856&#39;);<br>INSERT INTO course VALUES(&#39;9-888&#39;, &#39;高等数学&#39;, &#39;831&#39;);<br><br>-- 添加添加成绩表数据<br>INSERT INTO score VALUES(&#39;103&#39;, &#39;3-105&#39;, &#39;92&#39;);<br>INSERT INTO score VALUES(&#39;103&#39;, &#39;3-245&#39;, &#39;86&#39;);<br>INSERT INTO score VALUES(&#39;103&#39;, &#39;6-166&#39;, &#39;85&#39;);<br>INSERT INTO score VALUES(&#39;105&#39;, &#39;3-105&#39;, &#39;88&#39;);<br>INSERT INTO score VALUES(&#39;105&#39;, &#39;3-245&#39;, &#39;75&#39;);<br>INSERT INTO score VALUES(&#39;105&#39;, &#39;6-166&#39;, &#39;79&#39;);<br>INSERT INTO score VALUES(&#39;109&#39;, &#39;3-105&#39;, &#39;76&#39;);<br>INSERT INTO score VALUES(&#39;109&#39;, &#39;3-245&#39;, &#39;68&#39;);<br>INSERT INTO score VALUES(&#39;109&#39;, &#39;6-166&#39;, &#39;81&#39;);<br><br>-- 查看表结构<br>SELECT * FROM course;<br>SELECT * FROM score;<br>SELECT * FROM student;<br>SELECT * FROM teacher;<br></code></pre></td></tr></table></figure>

<h3 id="1-到-10"><a href="#1-到-10" class="headerlink" title="1 到 10"></a>1 到 10</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 查询 student 表的所有行<br>SELECT * FROM student;<br><br>-- 查询 student 表中的 name、sex 和 class 字段的所有行<br>SELECT name, sex, class FROM student;<br><br>-- 查询 teacher 表中不重复的 department 列<br>-- department: 去重查询关键词<br>SELECT DISTINCT department FROM teacher;<br><br>-- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）<br>-- BETWEEN xx AND xx: 查询区间, AND 表示 &quot;并且&quot;<br>SELECT * FROM score WHERE degree BETWEEN 60 AND 80;<br>SELECT * FROM score WHERE degree &gt; 60 AND degree &lt; 80;<br><br>-- 查询 score 表中成绩为 85, 86 或 88 的行<br>-- IN: 查询规定中的多个值 <br>SELECT * FROM score WHERE degree IN (85, 86, 88);<br><br>-- 查询 student 表中 &#39;95031&#39; 班或性别为 &#39;女&#39; 的所有行<br>-- or: 表示或者关系<br>SELECT * FROM student WHERE class &#x3D; &#39;95031&#39; or sex &#x3D; &#39;女&#39;;<br><br>-- 以 class 降序的方式查询 student 表的所有行<br>-- DESC: 降序，从高到低<br>-- ASC（默认）: 升序，从低到高<br>SELECT * FROM student ORDER BY class DESC;<br>SELECT * FROM student ORDER BY class ASC;<br><br>-- 以 c_no 升序、degree 降序查询 score 表的所有行<br>SELECT * FROM score ORDER BY c_no ASC, degree DESC;<br><br>-- 查询 &quot;95031&quot; 班的学生人数<br>-- COUNT: 统计<br>SELECT COUNT(*) FROM student WHERE class &#x3D; &#39;95031&#39;;<br><br>-- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。<br>-- (SELECT MAX(degree) FROM score): 子查询，算出最高分<br>SELECT s_no, c_no FROM score WHERE degree &#x3D; (SELECT MAX(degree) FROM score);<br>--拆分为两步<br>1、找到最高分 <br>select max(degree) from score;<br>2、找最高分的s_no c_no;<br>SELECT s_no, c_no FROM score WHERE degree &#x3D; (SELECT MAX(degree) FROM score);<br><br>--  排序查询<br>-- LIMIT r, n: 表示从第r行开始，查询n条数据<br>SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;<br></code></pre></td></tr></table></figure>

<h3 id="分组计算平均成绩"><a href="#分组计算平均成绩" class="headerlink" title="分组计算平均成绩"></a>分组计算平均成绩</h3><p><strong>查询每门课的平均成绩。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- AVG: 平均值<br>SELECT AVG(degree) FROM score WHERE c_no &#x3D; &#39;3-105&#39;;<br>SELECT AVG(degree) FROM score WHERE c_no &#x3D; &#39;3-245&#39;;<br>SELECT AVG(degree) FROM score WHERE c_no &#x3D; &#39;6-166&#39;;<br><br>-- GROUP BY: 分组查询<br>SELECT c_no, AVG(degree) FROM score GROUP BY c_no;<br></code></pre></td></tr></table></figure>

<h3 id="分组条件与模糊查询"><a href="#分组条件与模糊查询" class="headerlink" title="分组条件与模糊查询"></a>分组条件与模糊查询</h3><p><strong>查询 <code>score</code> 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM score;<br>-- c_no 课程编号<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-105 |     92 |<br>| 103  | 3-245 |     86 |<br>| 103  | 6-166 |     85 |<br>| 105  | 3-105 |     88 |<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<p>分析表发现，至少有 2 名学生选修的课程是 <code>3-105</code> 、<code>3-245</code> 、<code>6-166</code> ，以 3 开头的课程是 <code>3-105</code> 、<code>3-245</code> 。也就是说，我们要查询所有 <code>3-105</code> 和 <code>3-245</code> 的 <code>degree</code> 平均分。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 首先把 c_no, AVG(degree) 通过分组查询出来<br>SELECT c_no, AVG(degree) FROM score GROUP BY c_no<br>+-------+-------------+<br>| c_no  | AVG(degree) |<br>+-------+-------------+<br>| 3-105 |     85.3333 |<br>| 3-245 |     76.3333 |<br>| 6-166 |     81.6667 |<br>+-------+-------------+<br><br>-- 再查询出至少有 2 名学生选修的课程<br>-- HAVING: 表示持有<br>HAVING COUNT(c_no) &gt;&#x3D; 2<br><br>-- 并且是以 3 开头的课程<br>-- LIKE 表示模糊查询，&quot;%&quot; 是一个通配符，匹配 &quot;3&quot; 后面的任意字符。<br>AND c_no LIKE &#39;3%&#39;;<br><br>-- 完整的代码是 把前面的SQL语句拼接起来，<br>-- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。<br>SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no<br>HAVING COUNT(c_no) &gt;&#x3D; 2 AND c_no LIKE &#39;3%&#39;;<br>+-------+-------------+----------+<br>| c_no  | AVG(degree) | COUNT(*) |<br>+-------+-------------+----------+<br>| 3-105 |     85.3333 |        3 |<br>| 3-245 |     76.3333 |        3 |<br>+-------+-------------+----------+<br></code></pre></td></tr></table></figure>

<h3 id="多表查询-1"><a href="#多表查询-1" class="headerlink" title="多表查询 - 1"></a>多表查询 - 1</h3><p><strong>查询所有学生的 <code>name</code>，以及该学生在 <code>score</code> 表中对应的 <code>c_no</code> 和 <code>degree</code> 。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT no, name FROM student;<br>+-----+-----------+<br>| no  | name      |<br>+-----+-----------+<br>| 101 | 曾华      |<br>| 102 | 匡明      |<br>| 103 | 王丽      |<br>| 104 | 李军      |<br>| 105 | 王芳      |<br>| 106 | 陆军      |<br>| 107 | 王尼玛    |<br>| 108 | 张全蛋    |<br>| 109 | 赵铁柱    |<br>+-----+-----------+<br><br>SELECT s_no, c_no, degree FROM score;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-105 |     92 |<br>| 103  | 3-245 |     86 |<br>| 103  | 6-166 |     85 |<br>| 105  | 3-105 |     88 |<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<p>通过分析可以发现，只要把 <code>score</code> 表中的 <code>s_no</code> 字段值替换成 <code>student</code> 表中对应的 <code>name</code> 字段值就可以了，如何做呢？</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- FROM...: 表示从 student, score 表中查询<br>-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。<br>SELECT name, c_no, degree FROM student, score <br>WHERE student.no &#x3D; score.s_no;<br>+-----------+-------+--------+<br>| name      | c_no  | degree |<br>+-----------+-------+--------+<br>| 王丽      | 3-105 |     92 |<br>| 王丽      | 3-245 |     86 |<br>| 王丽      | 6-166 |     85 |<br>| 王芳      | 3-105 |     88 |<br>| 王芳      | 3-245 |     75 |<br>| 王芳      | 6-166 |     79 |<br>| 赵铁柱    | 3-105 |     76 |<br>| 赵铁柱    | 3-245 |     68 |<br>| 赵铁柱    | 6-166 |     81 |<br>+-----------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="多表查询-2"><a href="#多表查询-2" class="headerlink" title="多表查询 - 2"></a>多表查询 - 2</h3><p><strong>查询所有学生的 <code>no</code> 、课程名称 ( <code>course</code> 表中的 <code>name</code> ) 和成绩 ( <code>score</code> 表中的 <code>degree</code> ) 列。</strong></p>
<p>只有 <code>score</code> 关联学生的 <code>no</code> ，因此只要查询 <code>score</code> 表，就能找出所有和学生相关的 <code>no</code> 和 <code>degree</code> ：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT s_no, c_no, degree FROM score;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-105 |     92 |<br>| 103  | 3-245 |     86 |<br>| 103  | 6-166 |     85 |<br>| 105  | 3-105 |     88 |<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<p>然后查询 <code>course</code> 表：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs mysql">+-------+-----------------+<br>| no    | name            |<br>+-------+-----------------+<br>| 3-105 | 计算机导论      |<br>| 3-245 | 操作系统        |<br>| 6-166 | 数字电路        |<br>| 9-888 | 高等数学        |<br>+-------+-----------------+<br></code></pre></td></tr></table></figure>

<p>只要把 <code>score</code> 表中的 <code>c_no</code> 替换成 <code>course</code> 表中对应的 <code>name</code> 字段值就可以了。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。<br>-- as 表示取一个该字段的别名。<br>SELECT s_no, name as c_name, degree FROM score, course<br>WHERE score.c_no &#x3D; course.no;<br>+------+-----------------+--------+<br>| s_no | c_name          | degree |<br>+------+-----------------+--------+<br>| 103  | 计算机导论      |     92 |<br>| 105  | 计算机导论      |     88 |<br>| 109  | 计算机导论      |     76 |<br>| 103  | 操作系统        |     86 |<br>| 105  | 操作系统        |     75 |<br>| 109  | 操作系统        |     68 |<br>| 103  | 数字电路        |     85 |<br>| 105  | 数字电路        |     79 |<br>| 109  | 数字电路        |     81 |<br>+------+-----------------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="三表关联查询"><a href="#三表关联查询" class="headerlink" title="三表关联查询"></a>三表关联查询</h3><p><strong>查询所有学生的 <code>name</code> 、课程名 ( <code>course</code> 表中的 <code>name</code> ) 和 <code>degree</code> 。</strong></p>
<p>只有 <code>score</code> 表中关联学生的学号和课堂号，我们只要围绕着 <code>score</code> 这张表查询就好了。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM score;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-105 |     92 |<br>| 103  | 3-245 |     86 |<br>| 103  | 6-166 |     85 |<br>| 105  | 3-105 |     88 |<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<p>只要把 <code>s_no</code> 和 <code>c_no</code> 替换成 <code>student</code> 和 <code>srouse</code> 表中对应的 <code>name</code> 字段值就好了。</p>
<p>首先把 <code>s_no</code> 替换成 <code>student</code> 表中的 <code>name</code> 字段：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT name, c_no, degree FROM student, score WHERE student.no &#x3D; score.s_no;<br>+-----------+-------+--------+<br>| name      | c_no  | degree |<br>+-----------+-------+--------+<br>| 王丽      | 3-105 |     92 |<br>| 王丽      | 3-245 |     86 |<br>| 王丽      | 6-166 |     85 |<br>| 王芳      | 3-105 |     88 |<br>| 王芳      | 3-245 |     75 |<br>| 王芳      | 6-166 |     79 |<br>| 赵铁柱    | 3-105 |     76 |<br>| 赵铁柱    | 3-245 |     68 |<br>| 赵铁柱    | 6-166 |     81 |<br>+-----------+-------+--------+<br></code></pre></td></tr></table></figure>

<p>再把 <code>c_no</code> 替换成 <code>course</code> 表中的 <code>name</code> 字段：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 课程表<br>SELECT no, name FROM course;<br>+-------+-----------------+<br>| no    | name            |<br>+-------+-----------------+<br>| 3-105 | 计算机导论      |<br>| 3-245 | 操作系统        |<br>| 6-166 | 数字电路        |<br>| 9-888 | 高等数学        |<br>+-------+-----------------+<br><br>-- 由于字段名存在重复，使用 &quot;表名.字段名 as 别名&quot; 代替。<br>SELECT student.name as s_name, course.name as c_name, degree <br>FROM student, score, course<br>WHERE student.NO &#x3D; score.s_no<br>AND score.c_no &#x3D; course.no;<br></code></pre></td></tr></table></figure>

<h3 id="子查询加分组求平均分"><a href="#子查询加分组求平均分" class="headerlink" title="子查询加分组求平均分"></a>子查询加分组求平均分</h3><p><strong>查询 <code>95031</code> 班学生每门课程的平均成绩。</strong></p>
<p>在 <code>score</code> 表中根据 <code>student</code>  表的学生编号筛选出学生的课堂号和成绩：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- IN (..): 将筛选出的学生号当做 s_no 的条件查询<br>SELECT s_no, c_no, degree FROM score<br>WHERE s_no IN (SELECT no FROM student WHERE class &#x3D; &#39;95031&#39;);<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 105  | 3-105 |     88 |<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<p>这时只要将 <code>c_no</code> 分组一下就能得出 <code>95031</code> 班学生每门课的平均成绩：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT c_no, AVG(degree) FROM score<br>WHERE s_no IN (SELECT no FROM student WHERE class &#x3D; &#39;95031&#39;)<br>GROUP BY c_no;<br>+-------+-------------+<br>| c_no  | AVG(degree) |<br>+-------+-------------+<br>| 3-105 |     82.0000 |<br>| 3-245 |     71.5000 |<br>| 6-166 |     80.0000 |<br>+-------+-------------+<br></code></pre></td></tr></table></figure>

<h3 id="子查询-1"><a href="#子查询-1" class="headerlink" title="子查询 - 1"></a>子查询 - 1</h3><p><strong>查询在 <code>3-105</code> 课程中，所有成绩高于 <code>109</code> 号同学的记录。</strong></p>
<p>首先筛选出课堂号为 <code>3-105</code> ，在找出所有成绩高于 <code>109</code> 号同学的的行。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM score <br>WHERE c_no &#x3D; &#39;3-105&#39;<br>AND degree &gt; (SELECT degree FROM score WHERE s_no &#x3D; &#39;109&#39; AND c_no &#x3D; &#39;3-105&#39;);<br></code></pre></td></tr></table></figure>

<h3 id="子查询-2"><a href="#子查询-2" class="headerlink" title="子查询 - 2"></a>子查询 - 2</h3><p><strong>查询所有成绩高于 <code>109</code> 号同学的 <code>3-105</code> 课程成绩记录。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。<br>SELECT * FROM score<br>WHERE degree &gt; (SELECT degree FROM score WHERE s_no &#x3D; &#39;109&#39; AND c_no &#x3D; &#39;3-105&#39;);<br></code></pre></td></tr></table></figure>

<h3 id="YEAR-函数与带-IN-关键字查询"><a href="#YEAR-函数与带-IN-关键字查询" class="headerlink" title="YEAR 函数与带 IN 关键字查询"></a>YEAR 函数与带 IN 关键字查询</h3><p><strong>查询所有和 <code>101</code> 、<code>108</code> 号学生同年出生的 <code>no</code> 、<code>name</code> 、<code>birthday</code> 列。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- YEAR(..): 取出日期中的年份<br>SELECT no, name, birthday FROM student<br>WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));<br></code></pre></td></tr></table></figure>

<h3 id="多层嵌套子查询"><a href="#多层嵌套子查询" class="headerlink" title="多层嵌套子查询"></a>多层嵌套子查询</h3><p><strong>查询 <code>&#39;张旭&#39;</code> 教师任课的学生成绩表。</strong></p>
<p>首先找到教师编号：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT NO FROM teacher WHERE NAME &#x3D; &#39;张旭&#39;<br></code></pre></td></tr></table></figure>

<p>通过 <code>sourse</code> 表找到该教师课程号：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT NO FROM course WHERE t_no &#x3D; ( SELECT NO FROM teacher WHERE NAME &#x3D; &#39;张旭&#39; );<br></code></pre></td></tr></table></figure>

<p>通过筛选出的课程号查询成绩表：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM score WHERE c_no &#x3D; (<br>    SELECT no FROM course WHERE t_no &#x3D; ( <br>        SELECT no FROM teacher WHERE NAME &#x3D; &#39;张旭&#39; <br>    )<br>);<br></code></pre></td></tr></table></figure>

<h3 id="多表查询"><a href="#多表查询" class="headerlink" title="多表查询"></a>多表查询</h3><p><strong>查询某选修课程多于5个同学的教师姓名。</strong></p>
<p>首先在 <code>teacher</code> 表中，根据 <code>no</code> 字段来判断该教师的同一门课程是否有至少5名学员选修：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 查询 teacher 表<br>SELECT no, name FROM teacher;<br>+-----+--------+<br>| no  | name   |<br>+-----+--------+<br>| 804 | 李诚   |<br>| 825 | 王萍   |<br>| 831 | 刘冰   |<br>| 856 | 张旭   |<br>+-----+--------+<br><br>SELECT name FROM teacher WHERE no IN (<br>    -- 在这里找到对应的条件<br>);<br></code></pre></td></tr></table></figure>

<p>查看和教师编号有有关的表的信息：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM course;<br>-- t_no: 教师编号<br>+-------+-----------------+------+<br>| no    | name            | t_no |<br>+-------+-----------------+------+<br>| 3-105 | 计算机导论      | 825  |<br>| 3-245 | 操作系统        | 804  |<br>| 6-166 | 数字电路        | 856  |<br>| 9-888 | 高等数学        | 831  |<br>+-------+-----------------+------+<br></code></pre></td></tr></table></figure>

<p>我们已经找到和教师编号有关的字段就在 <code>course</code> 表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据 <code>score</code> 表来查询：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 在此之前向 score 插入一些数据，以便丰富查询条件。<br>INSERT INTO score VALUES (&#39;101&#39;, &#39;3-105&#39;, &#39;90&#39;);<br>INSERT INTO score VALUES (&#39;102&#39;, &#39;3-105&#39;, &#39;91&#39;);<br>INSERT INTO score VALUES (&#39;104&#39;, &#39;3-105&#39;, &#39;89&#39;);<br><br>-- 查询 score 表<br>SELECT * FROM score;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 101  | 3-105 |     90 |<br>| 102  | 3-105 |     91 |<br>| 103  | 3-105 |     92 |<br>| 103  | 3-245 |     86 |<br>| 103  | 6-166 |     85 |<br>| 104  | 3-105 |     89 |<br>| 105  | 3-105 |     88 |<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br><br>-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。<br>SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) &gt; 5;<br>+-------+<br>| c_no  |<br>+-------+<br>| 3-105 |<br>+-------+<br></code></pre></td></tr></table></figure>

<p>根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT t_no FROM course WHERE no IN (<br>    SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) &gt; 5<br>);<br>+------+<br>| t_no |<br>+------+<br>| 825  |<br>+------+<br></code></pre></td></tr></table></figure>

<p>在 <code>teacher</code> 表中，根据筛选出来的教师编号找到教师姓名：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT name FROM teacher WHERE no IN (<br>    -- 最终条件<br>    SELECT t_no FROM course WHERE no IN (<br>        SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) &gt; 5<br>    )<br>);<br></code></pre></td></tr></table></figure>

<h3 id="子查询-3"><a href="#子查询-3" class="headerlink" title="子查询 - 3"></a>子查询 - 3</h3><p><strong>查询 “计算机系” 课程的成绩表。</strong></p>
<p>思路是，先找出 <code>course</code> 表中所有 <code>计算机系</code> 课程的编号，然后根据这个编号查询 <code>score</code> 表。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 通过 teacher 表查询所有 &#96;计算机系&#96; 的教师编号<br>SELECT no, name, department FROM teacher WHERE department &#x3D; &#39;计算机系&#39;<br>+-----+--------+--------------+<br>| no  | name   | department   |<br>+-----+--------+--------------+<br>| 804 | 李诚   | 计算机系     |<br>| 825 | 王萍   | 计算机系     |<br>+-----+--------+--------------+<br><br>-- 通过 course 表查询该教师的课程编号<br>SELECT no FROM course WHERE t_no IN (<br>    SELECT no FROM teacher WHERE department &#x3D; &#39;计算机系&#39;<br>);<br>+-------+<br>| no    |<br>+-------+<br>| 3-245 |<br>| 3-105 |<br>+-------+<br><br>-- 根据筛选出来的课程号查询成绩表<br>SELECT * FROM score WHERE c_no IN (<br>    SELECT no FROM course WHERE t_no IN (<br>        SELECT no FROM teacher WHERE department &#x3D; &#39;计算机系&#39;<br>    )<br>);<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-245 |     86 |<br>| 105  | 3-245 |     75 |<br>| 109  | 3-245 |     68 |<br>| 101  | 3-105 |     90 |<br>| 102  | 3-105 |     91 |<br>| 103  | 3-105 |     92 |<br>| 104  | 3-105 |     89 |<br>| 105  | 3-105 |     88 |<br>| 109  | 3-105 |     76 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="UNION-和-NOTIN-的使用"><a href="#UNION-和-NOTIN-的使用" class="headerlink" title="UNION 和 NOTIN 的使用"></a>UNION 和 NOTIN 的使用</h3><p><strong>查询 <code>计算机系</code> 与 <code>电子工程系</code> 中的不同职称的教师。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- NOT: 代表逻辑非<br>SELECT * FROM teacher WHERE department &#x3D; &#39;计算机系&#39; AND profession NOT IN (<br>    SELECT profession FROM teacher WHERE department &#x3D; &#39;电子工程系&#39;<br>)<br>-- 合并两个集<br>UNION<br>SELECT * FROM teacher WHERE department &#x3D; &#39;电子工程系&#39; AND profession NOT IN (<br>    SELECT profession FROM teacher WHERE department &#x3D; &#39;计算机系&#39;<br>);<br></code></pre></td></tr></table></figure>

<h3 id="ANY-表示至少一个-DESC-降序"><a href="#ANY-表示至少一个-DESC-降序" class="headerlink" title="ANY 表示至少一个 - DESC ( 降序 )"></a>ANY 表示至少一个 - DESC ( 降序 )</h3><p><strong>查询课程 <code>3-105</code> 且成绩 <u>至少</u> 高于 <code>3-245</code> 的 <code>score</code> 表。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM score WHERE c_no &#x3D; &#39;3-105&#39;;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 101  | 3-105 |     90 |<br>| 102  | 3-105 |     91 |<br>| 103  | 3-105 |     92 |<br>| 104  | 3-105 |     89 |<br>| 105  | 3-105 |     88 |<br>| 109  | 3-105 |     76 |<br>+------+-------+--------+<br><br>SELECT * FROM score WHERE c_no &#x3D; &#39;3-245&#39;;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-245 |     86 |<br>| 105  | 3-245 |     75 |<br>| 109  | 3-245 |     68 |<br>+------+-------+--------+<br><br>-- ANY: 符合SQL语句中的任意条件。<br>-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，<br>-- 最后根据降序查询结果。<br>SELECT * FROM score WHERE c_no &#x3D; &#39;3-105&#39; AND degree &gt; ANY(<br>    SELECT degree FROM score WHERE c_no &#x3D; &#39;3-245&#39;<br>) ORDER BY degree DESC;<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-105 |     92 |<br>| 102  | 3-105 |     91 |<br>| 101  | 3-105 |     90 |<br>| 104  | 3-105 |     89 |<br>| 105  | 3-105 |     88 |<br>| 109  | 3-105 |     76 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="表示所有的-ALL"><a href="#表示所有的-ALL" class="headerlink" title="表示所有的 ALL"></a>表示所有的 ALL</h3><p><strong>查询课程 <code>3-105</code> 且成绩高于 <code>3-245</code> 的 <code>score</code> 表。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 只需对上一道题稍作修改。<br>-- ALL: 符合SQL语句中的所有条件。<br>-- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。<br>SELECT * FROM score WHERE c_no &#x3D; &#39;3-105&#39; AND degree &gt; ALL(<br>    SELECT degree FROM score WHERE c_no &#x3D; &#39;3-245&#39;<br>);<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 101  | 3-105 |     90 |<br>| 102  | 3-105 |     91 |<br>| 103  | 3-105 |     92 |<br>| 104  | 3-105 |     89 |<br>| 105  | 3-105 |     88 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="复制表的数据作为条件查询"><a href="#复制表的数据作为条件查询" class="headerlink" title="复制表的数据作为条件查询"></a>复制表的数据作为条件查询</h3><p><strong>查询某课程成绩比该课程平均成绩低的 <code>score</code> 表。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 查询平均分<br>SELECT c_no, AVG(degree) FROM score GROUP BY c_no;<br>+-------+-------------+<br>| c_no  | AVG(degree) |<br>+-------+-------------+<br>| 3-105 |     87.6667 |<br>| 3-245 |     76.3333 |<br>| 6-166 |     81.6667 |<br>+-------+-------------+<br><br>-- 查询 score 表<br>SELECT degree FROM score;<br>+--------+<br>| degree |<br>+--------+<br>|     90 |<br>|     91 |<br>|     92 |<br>|     86 |<br>|     85 |<br>|     89 |<br>|     88 |<br>|     75 |<br>|     79 |<br>|     76 |<br>|     68 |<br>|     81 |<br>+--------+<br><br>-- 将表 b 作用于表 a 中查询数据<br>-- score a (b): 将表声明为 a (b)，<br>-- 如此就能用 a.c_no &#x3D; b.c_no 作为条件执行查询了。<br>SELECT * FROM score a WHERE degree &lt; (<br>    (SELECT AVG(degree) FROM score b WHERE a.c_no &#x3D; b.c_no)<br>);<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 105  | 3-245 |     75 |<br>| 105  | 6-166 |     79 |<br>| 109  | 3-105 |     76 |<br>| 109  | 3-245 |     68 |<br>| 109  | 6-166 |     81 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="子查询-4"><a href="#子查询-4" class="headerlink" title="子查询 - 4"></a>子查询 - 4</h3><p><strong>查询所有任课 ( 在 <code>course</code> 表里有课程 ) 教师的 <code>name</code> 和 <code>department</code></strong> 。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);<br>+--------+-----------------+<br>| name   | department      |<br>+--------+-----------------+<br>| 李诚   | 计算机系        |<br>| 王萍   | 计算机系        |<br>| 刘冰   | 电子工程系      |<br>| 张旭   | 电子工程系      |<br>+--------+-----------------+<br></code></pre></td></tr></table></figure>

<h3 id="条件加组筛选"><a href="#条件加组筛选" class="headerlink" title="条件加组筛选"></a>条件加组筛选</h3><p><strong>查询 <code>student</code> 表中至少有 2 名男生的 <code>class</code> 。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 查看学生表信息<br>SELECT * FROM student;<br>+-----+-----------+-----+------------+-------+<br>| no  | name      | sex | birthday   | class |<br>+-----+-----------+-----+------------+-------+<br>| 101 | 曾华      | 男  | 1977-09-01 | 95033 |<br>| 102 | 匡明      | 男  | 1975-10-02 | 95031 |<br>| 103 | 王丽      | 女  | 1976-01-23 | 95033 |<br>| 104 | 李军      | 男  | 1976-02-20 | 95033 |<br>| 105 | 王芳      | 女  | 1975-02-10 | 95031 |<br>| 106 | 陆军      | 男  | 1974-06-03 | 95031 |<br>| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |<br>| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |<br>| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |<br>| 110 | 张飞      | 男  | 1974-06-03 | 95038 |<br>+-----+-----------+-----+------------+-------+<br><br>-- 只查询性别为男，然后按 class 分组，并限制 class 行大于 1。<br>SELECT class FROM student WHERE sex &#x3D; &#39;男&#39; GROUP BY class HAVING COUNT(*) &gt; 1;<br>+-------+<br>| class |<br>+-------+<br>| 95033 |<br>| 95031 |<br>+-------+<br></code></pre></td></tr></table></figure>

<h3 id="NOTLIKE-模糊查询取反"><a href="#NOTLIKE-模糊查询取反" class="headerlink" title="NOTLIKE 模糊查询取反"></a>NOTLIKE 模糊查询取反</h3><p><strong>查询 <code>student</code> 表中不姓 “王” 的同学记录。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- NOT: 取反<br>-- LIKE: 模糊查询<br>mysql&gt; SELECT * FROM student WHERE name NOT LIKE &#39;王%&#39;;<br>+-----+-----------+-----+------------+-------+<br>| no  | name      | sex | birthday   | class |<br>+-----+-----------+-----+------------+-------+<br>| 101 | 曾华      | 男  | 1977-09-01 | 95033 |<br>| 102 | 匡明      | 男  | 1975-10-02 | 95031 |<br>| 104 | 李军      | 男  | 1976-02-20 | 95033 |<br>| 106 | 陆军      | 男  | 1974-06-03 | 95031 |<br>| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |<br>| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |<br>| 110 | 张飞      | 男  | 1974-06-03 | 95038 |<br>+-----+-----------+-----+------------+-------+<br></code></pre></td></tr></table></figure>

<h3 id="YEAR-与-NOW-函数"><a href="#YEAR-与-NOW-函数" class="headerlink" title="YEAR 与 NOW 函数"></a>YEAR 与 NOW 函数</h3><p><strong>查询 <code>student</code> 表中每个学生的姓名和年龄。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。<br>SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;<br>+-----------+------+<br>| name      | age  |<br>+-----------+------+<br>| 曾华      |   42 |<br>| 匡明      |   44 |<br>| 王丽      |   43 |<br>| 李军      |   43 |<br>| 王芳      |   44 |<br>| 陆军      |   45 |<br>| 王尼玛    |   43 |<br>| 张全蛋    |   44 |<br>| 赵铁柱    |   45 |<br>| 张飞      |   45 |<br>+-----------+------+<br></code></pre></td></tr></table></figure>

<h3 id="MAX-与-MIN-函数"><a href="#MAX-与-MIN-函数" class="headerlink" title="MAX 与 MIN 函数"></a>MAX 与 MIN 函数</h3><p><strong>查询 <code>student</code> 表中最大和最小的 <code>birthday</code> 值。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT MAX(birthday), MIN(birthday) FROM student;<br>+---------------+---------------+<br>| MAX(birthday) | MIN(birthday) |<br>+---------------+---------------+<br>| 1977-09-01    | 1974-06-03    |<br>+---------------+---------------+<br></code></pre></td></tr></table></figure>

<h3 id="多段排序"><a href="#多段排序" class="headerlink" title="多段排序"></a>多段排序</h3><p><strong>以 <code>class</code> 和 <code>birthday</code> 从大到小的顺序查询 <code>student</code> 表。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM student ORDER BY class DESC, birthday;<br>+-----+-----------+-----+------------+-------+<br>| no  | name      | sex | birthday   | class |<br>+-----+-----------+-----+------------+-------+<br>| 110 | 张飞      | 男  | 1974-06-03 | 95038 |<br>| 103 | 王丽      | 女  | 1976-01-23 | 95033 |<br>| 104 | 李军      | 男  | 1976-02-20 | 95033 |<br>| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |<br>| 101 | 曾华      | 男  | 1977-09-01 | 95033 |<br>| 106 | 陆军      | 男  | 1974-06-03 | 95031 |<br>| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |<br>| 105 | 王芳      | 女  | 1975-02-10 | 95031 |<br>| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |<br>| 102 | 匡明      | 男  | 1975-10-02 | 95031 |<br>+-----+-----------+-----+------------+-------+<br></code></pre></td></tr></table></figure>

<h3 id="子查询-5"><a href="#子查询-5" class="headerlink" title="子查询 - 5"></a>子查询 - 5</h3><p><strong>查询 “男” 教师及其所上的课程。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex &#x3D; &#39;男&#39;);<br>+-------+--------------+------+<br>| no    | name         | t_no |<br>+-------+--------------+------+<br>| 3-245 | 操作系统     | 804  |<br>| 6-166 | 数字电路     | 856  |<br>+-------+--------------+------+<br></code></pre></td></tr></table></figure>

<h3 id="MAX-函数与子查询"><a href="#MAX-函数与子查询" class="headerlink" title="MAX 函数与子查询"></a>MAX 函数与子查询</h3><p><strong>查询最高分同学的 <code>score</code> 表。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 找出最高成绩（该查询只能有一个结果）<br>SELECT MAX(degree) FROM score;<br><br>-- 根据上面的条件筛选出所有最高成绩表，<br>-- 该查询可能有多个结果，假设 degree 值多次符合条件。<br>SELECT * FROM score WHERE degree &#x3D; (SELECT MAX(degree) FROM score);<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 103  | 3-105 |     92 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="子查询-6"><a href="#子查询-6" class="headerlink" title="子查询 - 6"></a>子查询 - 6</h3><p><strong>查询和 “李军” 同性别的所有同学 <code>name</code> 。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 首先将李军的性别作为条件取出来<br>SELECT sex FROM student WHERE name &#x3D; &#39;李军&#39;;<br>+-----+<br>| sex |<br>+-----+<br>| 男  |<br>+-----+<br><br>-- 根据性别查询 name 和 sex<br>SELECT name, sex FROM student WHERE sex &#x3D; (<br>    SELECT sex FROM student WHERE name &#x3D; &#39;李军&#39;<br>);<br>+-----------+-----+<br>| name      | sex |<br>+-----------+-----+<br>| 曾华      | 男  |<br>| 匡明      | 男  |<br>| 李军      | 男  |<br>| 陆军      | 男  |<br>| 王尼玛    | 男  |<br>| 张全蛋    | 男  |<br>| 赵铁柱    | 男  |<br>| 张飞      | 男  |<br>+-----------+-----+<br></code></pre></td></tr></table></figure>

<h3 id="子查询-7"><a href="#子查询-7" class="headerlink" title="子查询 - 7"></a>子查询 - 7</h3><p><strong>查询和 “李军” 同性别且同班的同学 <code>name</code> 。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT name, sex, class FROM student WHERE sex &#x3D; (<br>    SELECT sex FROM student WHERE name &#x3D; &#39;李军&#39;<br>) AND class &#x3D; (<br>    SELECT class FROM student WHERE name &#x3D; &#39;李军&#39;<br>);<br>+-----------+-----+-------+<br>| name      | sex | class |<br>+-----------+-----+-------+<br>| 曾华      | 男  | 95033 |<br>| 李军      | 男  | 95033 |<br>| 王尼玛    | 男  | 95033 |<br>+-----------+-----+-------+<br></code></pre></td></tr></table></figure>

<h3 id="子查询-8"><a href="#子查询-8" class="headerlink" title="子查询 - 8"></a>子查询 - 8</h3><p><strong>查询所有选修 “计算机导论” 课程的 “男” 同学成绩表。</strong></p>
<p>需要的 “计算机导论” 和性别为 “男” 的编号可以在 <code>course</code> 和 <code>student</code> 表中找到。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM score WHERE c_no &#x3D; (<br>    SELECT no FROM course WHERE name &#x3D; &#39;计算机导论&#39;<br>) AND s_no IN (<br>    SELECT no FROM student WHERE sex &#x3D; &#39;男&#39;<br>);<br>+------+-------+--------+<br>| s_no | c_no  | degree |<br>+------+-------+--------+<br>| 101  | 3-105 |     90 |<br>| 102  | 3-105 |     91 |<br>| 104  | 3-105 |     89 |<br>| 109  | 3-105 |     76 |<br>+------+-------+--------+<br></code></pre></td></tr></table></figure>

<h3 id="按等级查询"><a href="#按等级查询" class="headerlink" title="按等级查询"></a>按等级查询</h3><p>建立一个 <code>grade</code> 表代表学生的成绩等级，并插入数据：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><code class="hljs mysql">CREATE TABLE grade (<br>    low INT(3),<br>    upp INT(3),<br>    grade char(1)<br>);<br><br>INSERT INTO grade VALUES (90, 100, &#39;A&#39;);<br>INSERT INTO grade VALUES (80, 89, &#39;B&#39;);<br>INSERT INTO grade VALUES (70, 79, &#39;C&#39;);<br>INSERT INTO grade VALUES (60, 69, &#39;D&#39;);<br>INSERT INTO grade VALUES (0, 59, &#39;E&#39;);<br><br>SELECT * FROM grade;<br>+------+------+-------+<br>| low  | upp  | grade |<br>+------+------+-------+<br>|   90 |  100 | A     |<br>|   80 |   89 | B     |<br>|   70 |   79 | C     |<br>|   60 |   69 | D     |<br>|    0 |   59 | E     |<br>+------+------+-------+<br></code></pre></td></tr></table></figure>

<p><strong>查询所有学生的 <code>s_no</code> 、<code>c_no</code> 和 <code>grade</code> 列。</strong></p>
<p>思路是，使用区间 ( <code>BETWEEN</code> ) 查询，判断学生的成绩 ( <code>degree</code> )  在 <code>grade</code> 表的 <code>low</code> 和 <code>upp</code> 之间。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT s_no, c_no, grade FROM score, grade <br>WHERE degree BETWEEN low AND upp;<br>+------+-------+-------+<br>| s_no | c_no  | grade |<br>+------+-------+-------+<br>| 101  | 3-105 | A     |<br>| 102  | 3-105 | A     |<br>| 103  | 3-105 | A     |<br>| 103  | 3-245 | B     |<br>| 103  | 6-166 | B     |<br>| 104  | 3-105 | B     |<br>| 105  | 3-105 | B     |<br>| 105  | 3-245 | C     |<br>| 105  | 6-166 | C     |<br>| 109  | 3-105 | C     |<br>| 109  | 3-245 | D     |<br>| 109  | 6-166 | B     |<br>+------+-------+-------+<br></code></pre></td></tr></table></figure>

<h3 id="连接查询"><a href="#连接查询" class="headerlink" title="连接查询"></a>连接查询</h3><p>准备用于测试连接查询的数据：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><code class="hljs mysql">CREATE DATABASE testJoin;<br><br>CREATE TABLE person (<br>    id INT,<br>    name VARCHAR(20),<br>    cardId INT<br>);<br><br>CREATE TABLE card (<br>    id INT,<br>    name VARCHAR(20)<br>);<br><br>INSERT INTO card VALUES (1, &#39;饭卡&#39;), (2, &#39;建行卡&#39;), (3, &#39;农行卡&#39;), (4, &#39;工商卡&#39;), (5, &#39;邮政卡&#39;);<br>SELECT * FROM card;<br>+------+-----------+<br>| id   | name      |<br>+------+-----------+<br>|    1 | 饭卡      |<br>|    2 | 建行卡    |<br>|    3 | 农行卡    |<br>|    4 | 工商卡    |<br>|    5 | 邮政卡    |<br>+------+-----------+<br><br>INSERT INTO person VALUES (1, &#39;张三&#39;, 1), (2, &#39;李四&#39;, 3), (3, &#39;王五&#39;, 6);<br>SELECT * FROM person;<br>+------+--------+--------+<br>| id   | name   | cardId |<br>+------+--------+--------+<br>|    1 | 张三   |      1 |<br>|    2 | 李四   |      3 |<br>|    3 | 王五   |      6 |<br>+------+--------+--------+<br></code></pre></td></tr></table></figure>

<p>分析两张表发现，<code>person</code> 表并没有为 <code>cardId</code> 字段设置一个在 <code>card</code> 表中对应的 <code>id</code> 外键。如果设置了的话，<code>person</code> 中 <code>cardId</code> 字段值为 <code>6</code> 的行就插不进去，因为该 <code>cardId</code> 值在 <code>card</code> 表中并没有。</p>
<h4 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h4><p>要查询这两张表中有关系的数据，可以使用 <code>INNER JOIN</code> ( 内连接 ) 将它们连接在一起。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- INNER JOIN: 表示为内连接，将两张表拼接在一起。<br>-- on: 表示要执行某个条件。<br>SELECT * FROM person INNER JOIN card on person.cardId &#x3D; card.id;<br>+------+--------+--------+------+-----------+<br>| id   | name   | cardId | id   | name      |<br>+------+--------+--------+------+-----------+<br>|    1 | 张三   |      1 |    1 | 饭卡      |<br>|    2 | 李四   |      3 |    3 | 农行卡    |<br>+------+--------+--------+------+-----------+<br><br>-- 将 INNER 关键字省略掉，结果也是一样的。<br>-- SELECT * FROM person JOIN card on person.cardId &#x3D; card.id;<br></code></pre></td></tr></table></figure>

<blockquote>
<p>注意：<code>card</code> 的整张表被连接到了右边。</p>
</blockquote>
<h4 id="左外连接"><a href="#左外连接" class="headerlink" title="左外连接"></a>左外连接</h4><p>完整显示左边的表 ( <code>person</code> ) ，右边的表如果符合条件就显示，不符合则补 <code>NULL</code> 。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。<br>SELECT * FROM person LEFT JOIN card on person.cardId &#x3D; card.id;<br>+------+--------+--------+------+-----------+<br>| id   | name   | cardId | id   | name      |<br>+------+--------+--------+------+-----------+<br>|    1 | 张三   |      1 |    1 | 饭卡      |<br>|    2 | 李四   |      3 |    3 | 农行卡    |<br>|    3 | 王五   |      6 | NULL | NULL      |<br>+------+--------+--------+------+-----------+<br></code></pre></td></tr></table></figure>

<h4 id="右外链接"><a href="#右外链接" class="headerlink" title="右外链接"></a>右外链接</h4><p>完整显示右边的表 ( <code>card</code> ) ，左边的表如果符合条件就显示，不符合则补 <code>NULL</code> 。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SELECT * FROM person RIGHT JOIN card on person.cardId &#x3D; card.id;<br>+------+--------+--------+------+-----------+<br>| id   | name   | cardId | id   | name      |<br>+------+--------+--------+------+-----------+<br>|    1 | 张三   |      1 |    1 | 饭卡      |<br>|    2 | 李四   |      3 |    3 | 农行卡    |<br>| NULL | NULL   |   NULL |    2 | 建行卡    |<br>| NULL | NULL   |   NULL |    4 | 工商卡    |<br>| NULL | NULL   |   NULL |    5 | 邮政卡    |<br>+------+--------+--------+------+-----------+<br></code></pre></td></tr></table></figure>

<h4 id="全外链接"><a href="#全外链接" class="headerlink" title="全外链接"></a>全外链接</h4><p>完整显示两张表的全部数据。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- MySQL 不支持这种语法的全外连接<br>-- SELECT * FROM person FULL JOIN card on person.cardId &#x3D; card.id;<br>-- 出现错误：<br>-- ERROR 1054 (42S22): Unknown column &#39;person.cardId&#39; in &#39;on clause&#39;<br><br>-- MySQL全连接语法，使用 UNION 将两张表合并在一起。<br>SELECT * FROM person LEFT JOIN card on person.cardId &#x3D; card.id<br>UNION<br>SELECT * FROM person RIGHT JOIN card on person.cardId &#x3D; card.id;<br>+------+--------+--------+------+-----------+<br>| id   | name   | cardId | id   | name      |<br>+------+--------+--------+------+-----------+<br>|    1 | 张三   |      1 |    1 | 饭卡      |<br>|    2 | 李四   |      3 |    3 | 农行卡    |<br>|    3 | 王五   |      6 | NULL | NULL      |<br>| NULL | NULL   |   NULL |    2 | 建行卡    |<br>| NULL | NULL   |   NULL |    4 | 工商卡    |<br>| NULL | NULL   |   NULL |    5 | 邮政卡    |<br>+------+--------+--------+------+-----------+<br></code></pre></td></tr></table></figure>

<h2 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h2><p>在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够<strong>保证一个业务的完整性</strong>。</p>
<p>比如我们的银行转账：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- a -&gt; -100<br>UPDATE user set money &#x3D; money - 100 WHERE name &#x3D; &#39;a&#39;;<br><br>-- b -&gt; +100<br>UPDATE user set money &#x3D; money + 100 WHERE name &#x3D; &#39;b&#39;;<br></code></pre></td></tr></table></figure>

<p>在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。</p>
<p>因此，在执行多条有关联 SQL 语句时，<strong>事务</strong>可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。</p>
<h3 id="如何控制事务-COMMIT-ROLLBACK"><a href="#如何控制事务-COMMIT-ROLLBACK" class="headerlink" title="如何控制事务 - COMMIT / ROLLBACK"></a>如何控制事务 - COMMIT / ROLLBACK</h3><p>在 MySQL 中，事务的<strong>自动提交</strong>状态默认是开启的。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 查询事务的自动提交状态<br>SELECT @@AUTOCOMMIT;<br>+--------------+<br>| @@AUTOCOMMIT |<br>+--------------+<br>|            1 |<br>+--------------+<br></code></pre></td></tr></table></figure>

<p><strong>自动提交的作用</strong>：当我们执行一条 SQL 语句的时候，其产生的效果就会立即体现出来，且不能<strong>回滚</strong>。</p>
<p>什么是回滚？举个例子：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs mysql">CREATE DATABASE bank;<br><br>USE bank;<br><br>CREATE TABLE user (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(20),<br>    money INT<br>);<br><br>INSERT INTO user VALUES (1, &#39;a&#39;, 1000);<br><br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<p>可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它<strong>提交</strong>到了数据库中。那么所谓<strong>回滚</strong>的意思就是，撤销执行过的所有 SQL 语句，使其回滚到<strong>最后一次提交</strong>数据时的状态。</p>
<p>在 MySQL 中使用 <code>ROLLBACK</code> 执行回滚：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 回滚到最后一次提交<br>ROLLBACK;<br><br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<p>由于所有执行过的 SQL 语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 关闭自动提交<br>SET AUTOCOMMIT &#x3D; 0;<br><br>-- 查询自动提交状态<br>SELECT @@AUTOCOMMIT;<br>+--------------+<br>| @@AUTOCOMMIT |<br>+--------------+<br>|            0 |<br>+--------------+<br></code></pre></td></tr></table></figure>

<p>将自动提交关闭后，测试数据回滚：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><code class="hljs mysql">INSERT INTO user VALUES (2, &#39;b&#39;, 1000);<br><br>-- 关闭 AUTOCOMMIT 后，数据的变化是在一张虚拟的临时数据表中展示，<br>-- 发生变化的数据并没有真正插入到数据表中。<br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>|  2 | b    |  1000 |<br>+----+------+-------+<br><br>-- 数据表中的真实数据其实还是：<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>+----+------+-------+<br><br>-- 由于数据还没有真正提交，可以使用回滚<br>ROLLBACK;<br><br>-- 再次查询<br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<p>那如何将虚拟的数据真正提交到数据库中？使用 <code>COMMIT</code> : </p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><code class="hljs mysql">INSERT INTO user VALUES (2, &#39;b&#39;, 1000);<br>-- 手动提交数据（持久性），<br>-- 将数据真正提交到数据库中，执行后不能再回滚提交过的数据。<br>COMMIT;<br><br>-- 提交后测试回滚<br>ROLLBACK;<br><br>-- 再次查询（回滚无效了）<br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>|  2 | b    |  1000 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<blockquote>
<p><strong>总结</strong></p>
<ol>
<li><p><strong>自动提交</strong></p>
<ul>
<li><p>查看自动提交状态：<code>SELECT @@AUTOCOMMIT</code> ；</p>
</li>
<li><p>设置自动提交状态：<code>SET AUTOCOMMIT = 0</code> 。</p>
</li>
</ul>
</li>
<li><p><strong>手动提交</strong></p>
<p><code>@@AUTOCOMMIT = 0</code> 时，使用 <code>COMMIT</code> 命令提交事务。</p>
</li>
<li><p><strong>事务回滚</strong></p>
<p><code>@@AUTOCOMMIT = 0</code> 时，使用 <code>ROLLBACK</code> 命令回滚事务。</p>
</li>
</ol>
</blockquote>
<p><strong>事务的实际应用</strong>，让我们再回到银行转账项目：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 转账<br>UPDATE user set money &#x3D; money - 100 WHERE name &#x3D; &#39;a&#39;;<br><br>-- 到账<br>UPDATE user set money &#x3D; money + 100 WHERE name &#x3D; &#39;b&#39;;<br><br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |   900 |<br>|  2 | b    |  1100 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<p>这时假设在转账时发生了意外，就可以使用 <code>ROLLBACK</code> 回滚到最后一次提交的状态：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 假设转账发生了意外，需要回滚。<br>ROLLBACK;<br><br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>|  2 | b    |  1000 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<p>这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：<code>COMMIT</code> 。</p>
<h3 id="手动开启事务-BEGIN-START-TRANSACTION"><a href="#手动开启事务-BEGIN-START-TRANSACTION" class="headerlink" title="手动开启事务 - BEGIN / START TRANSACTION"></a>手动开启事务 - BEGIN / START TRANSACTION</h3><p>事务的默认提交被开启 ( <code>@@AUTOCOMMIT = 1</code> ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务<br>-- START TRANSACTION;<br>BEGIN;<br>UPDATE user set money &#x3D; money - 100 WHERE name &#x3D; &#39;a&#39;;<br>UPDATE user set money &#x3D; money + 100 WHERE name &#x3D; &#39;b&#39;;<br><br>-- 由于手动开启的事务没有开启自动提交，<br>-- 此时发生变化的数据仍然是被保存在一张临时表中。<br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |   900 |<br>|  2 | b    |  1100 |<br>+----+------+-------+<br><br>-- 测试回滚<br>ROLLBACK;<br><br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |  1000 |<br>|  2 | b    |  1000 |<br>+----+------+-------+<br></code></pre></td></tr></table></figure>

<p>仍然使用 <code>COMMIT</code> 提交数据，提交后无法再发生本次事务的回滚。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs mysql">BEGIN;<br>UPDATE user set money &#x3D; money - 100 WHERE name &#x3D; &#39;a&#39;;<br>UPDATE user set money &#x3D; money + 100 WHERE name &#x3D; &#39;b&#39;;<br><br>SELECT * FROM user;<br>+----+------+-------+<br>| id | name | money |<br>+----+------+-------+<br>|  1 | a    |   900 |<br>|  2 | b    |  1100 |<br>+----+------+-------+<br><br>-- 提交数据<br>COMMIT;<br><br>-- 测试回滚（无效，因为表的数据已经被提交）<br>ROLLBACK;<br></code></pre></td></tr></table></figure>

<h3 id="事务的-ACID-特征与使用"><a href="#事务的-ACID-特征与使用" class="headerlink" title="事务的 ACID 特征与使用"></a>事务的 ACID 特征与使用</h3><p><strong>事务的四大特征：</strong></p>
<ul>
<li><strong>A 原子性</strong>：事务是最小的单位，不可以再分割；</li>
<li><strong>C 一致性</strong>：要求同一事务中的 SQL 语句，必须保证同时成功或者失败；</li>
<li><strong>I 隔离性</strong>：事务1 和 事务2 之间是具有隔离性的；</li>
<li><strong>D 持久性</strong>：事务一旦结束 ( <code>COMMIT</code> ) ，就不可以再返回了 ( <code>ROLLBACK</code> ) 。</li>
</ul>
<h3 id="事务的隔离性"><a href="#事务的隔离性" class="headerlink" title="事务的隔离性"></a>事务的隔离性</h3><p><strong>事务的隔离性可分为四种 ( 性能从低到高 )</strong> ：</p>
<ol>
<li><p><strong>READ UNCOMMITTED ( 读取未提交 )</strong></p>
<p>如果有多个事务，那么任意事务都可以看见其他事务的<strong>未提交数据</strong>。</p>
</li>
<li><p><strong>READ COMMITTED ( 读取已提交 )</strong></p>
<p>只能读取到其他事务<strong>已经提交的数据</strong>。</p>
</li>
<li><p><strong>REPEATABLE READ ( 可被重复读 )</strong></p>
<p>如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。</p>
</li>
<li><p><strong>SERIALIZABLE ( 串行化 )</strong></p>
<p>所有的事务都会按照<strong>固定顺序执行</strong>，执行完一个事务后再继续执行下一个事务的<strong>写入操作</strong>。</p>
</li>
</ol>
<p>查看当前数据库的默认隔离级别：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- MySQL 8.x, GLOBAL 表示系统级别，不加表示会话级别。<br>SELECT @@GLOBAL.TRANSACTION_ISOLATION;<br>SELECT @@TRANSACTION_ISOLATION;<br>+--------------------------------+<br>| @@GLOBAL.TRANSACTION_ISOLATION |<br>+--------------------------------+<br>| REPEATABLE-READ                | -- MySQL的默认隔离级别，可以重复读。<br>+--------------------------------+<br><br>-- MySQL 5.x<br>SELECT @@GLOBAL.TX_ISOLATION;<br>SELECT @@TX_ISOLATION;<br></code></pre></td></tr></table></figure>

<p>修改隔离级别：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。<br>SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;<br><br>-- 查询系统隔离级别，发现已经被修改。<br>SELECT @@GLOBAL.TRANSACTION_ISOLATION;<br>+--------------------------------+<br>| @@GLOBAL.TRANSACTION_ISOLATION |<br>+--------------------------------+<br>| READ-UNCOMMITTED               |<br>+--------------------------------+<br></code></pre></td></tr></table></figure>

<h4 id="脏读"><a href="#脏读" class="headerlink" title="脏读"></a>脏读</h4><p>测试 <strong>READ UNCOMMITTED ( 读取未提交 )</strong> 的隔离性：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><code class="hljs mysql">INSERT INTO user VALUES (3, &#39;小明&#39;, 1000);<br>INSERT INTO user VALUES (4, &#39;淘宝店&#39;, 1000);<br><br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |  1000 |<br>|  4 | 淘宝店    |  1000 |<br>+----+-----------+-------+<br><br>-- 开启一个事务操作数据<br>-- 假设小明在淘宝店买了一双800块钱的鞋子：<br>START TRANSACTION;<br>UPDATE user SET money &#x3D; money - 800 WHERE name &#x3D; &#39;小明&#39;;<br>UPDATE user SET money &#x3D; money + 800 WHERE name &#x3D; &#39;淘宝店&#39;;<br><br>-- 然后淘宝店在另一方查询结果，发现钱已到账。<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |   200 |<br>|  4 | 淘宝店    |  1800 |<br>+----+-----------+-------+<br></code></pre></td></tr></table></figure>

<p>由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 <code>ROLLBACK</code> 命令，会发生什么？</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 小明所处的事务<br>ROLLBACK;<br><br>-- 此时无论对方是谁，如果再去查询结果就会发现：<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |  1000 |<br>|  4 | 淘宝店    |  1000 |<br>+----+-----------+-------+<br></code></pre></td></tr></table></figure>

<p>这就是所谓的<strong>脏读</strong>，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。</p>
<h4 id="读取已提交"><a href="#读取已提交" class="headerlink" title="读取已提交"></a>读取已提交</h4><p>把隔离级别设置为 <strong>READ COMMITTED</strong> ：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;<br>SELECT @@GLOBAL.TRANSACTION_ISOLATION;<br>+--------------------------------+<br>| @@GLOBAL.TRANSACTION_ISOLATION |<br>+--------------------------------+<br>| READ-COMMITTED                 |<br>+--------------------------------+<br></code></pre></td></tr></table></figure>

<p>这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 正在操作数据事务（当前事务）<br>START TRANSACTION;<br>UPDATE user SET money &#x3D; money - 800 WHERE name &#x3D; &#39;小明&#39;;<br>UPDATE user SET money &#x3D; money + 800 WHERE name &#x3D; &#39;淘宝店&#39;;<br><br>-- 虽然隔离级别被设置为了 READ COMMITTED，但在当前事务中，<br>-- 它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |   200 |<br>|  4 | 淘宝店    |  1800 |<br>+----+-----------+-------+<br><br><br>-- 假设此时在远程开启了一个新事务，连接到数据库。<br>$ mysql -u root -p12345612<br><br>-- 此时远程连接查询到的数据只能是已经提交过的<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |  1000 |<br>|  4 | 淘宝店    |  1000 |<br>+----+-----------+-------+<br></code></pre></td></tr></table></figure>

<p>但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 小张在查询数据的时候发现：<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |   200 |<br>|  4 | 淘宝店    |  1800 |<br>+----+-----------+-------+<br><br>-- 在小张求表的 money 平均值之前，小王做了一个操作：<br>START TRANSACTION;<br>INSERT INTO user VALUES (5, &#39;c&#39;, 100);<br>COMMIT;<br><br>-- 此时表的真实数据是：<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |  1000 |<br>|  4 | 淘宝店    |  1000 |<br>|  5 | c         |   100 |<br>+----+-----------+-------+<br><br>-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：<br>SELECT AVG(money) FROM user;<br>+------------+<br>| AVG(money) |<br>+------------+<br>|  820.0000  |<br>+------------+<br></code></pre></td></tr></table></figure>

<p>虽然 <strong>READ COMMITTED</strong> 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是<strong>在读取同一个表的数据时，可能会发生前后不一致的情况。</strong>这被称为<strong>不可重复读现象 ( READ COMMITTED )</strong> 。</p>
<h4 id="幻读"><a href="#幻读" class="headerlink" title="幻读"></a>幻读</h4><p>将隔离级别设置为 <strong>REPEATABLE READ ( 可被重复读取 )</strong> :</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;<br>SELECT @@GLOBAL.TRANSACTION_ISOLATION;<br>+--------------------------------+<br>| @@GLOBAL.TRANSACTION_ISOLATION |<br>+--------------------------------+<br>| REPEATABLE-READ                |<br>+--------------------------------+<br></code></pre></td></tr></table></figure>

<p>测试 <strong>REPEATABLE READ</strong> ，假设在两个不同的连接上分别执行 <code>START TRANSACTION</code> :</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-comment">-- 小张 - 成都</span><br><span class="hljs-keyword">START</span> TRANSACTION;<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">6</span>, <span class="hljs-string">&#x27;d&#x27;</span>, <span class="hljs-number">1000</span>);<br><br><span class="hljs-comment">-- 小王 - 北京</span><br><span class="hljs-keyword">START</span> TRANSACTION;<br><br><span class="hljs-comment">-- 小张 - 成都</span><br><span class="hljs-keyword">COMMIT</span>;<br></code></pre></td></tr></table></figure>

<p>当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。</p>
<p>无论小张是否执行过 <code>COMMIT</code> ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">user</span>;<br><span class="hljs-operator">+</span><span class="hljs-comment">----+-----------+-------+</span><br><span class="hljs-operator">|</span> id <span class="hljs-operator">|</span> name      <span class="hljs-operator">|</span> money <span class="hljs-operator">|</span><br><span class="hljs-operator">+</span><span class="hljs-comment">----+-----------+-------+</span><br><span class="hljs-operator">|</span>  <span class="hljs-number">1</span> <span class="hljs-operator">|</span> a         <span class="hljs-operator">|</span>   <span class="hljs-number">900</span> <span class="hljs-operator">|</span><br><span class="hljs-operator">|</span>  <span class="hljs-number">2</span> <span class="hljs-operator">|</span> b         <span class="hljs-operator">|</span>  <span class="hljs-number">1100</span> <span class="hljs-operator">|</span><br><span class="hljs-operator">|</span>  <span class="hljs-number">3</span> <span class="hljs-operator">|</span> 小明      <span class="hljs-operator">|</span>  <span class="hljs-number">1000</span> <span class="hljs-operator">|</span><br><span class="hljs-operator">|</span>  <span class="hljs-number">4</span> <span class="hljs-operator">|</span> 淘宝店    <span class="hljs-operator">|</span>  <span class="hljs-number">1000</span> <span class="hljs-operator">|</span><br><span class="hljs-operator">|</span>  <span class="hljs-number">5</span> <span class="hljs-operator">|</span> c         <span class="hljs-operator">|</span>   <span class="hljs-number">100</span> <span class="hljs-operator">|</span><br><span class="hljs-operator">+</span><span class="hljs-comment">----+-----------+-------+</span><br></code></pre></td></tr></table></figure>

<p>这是<strong>因为小王在此之前开启了一个新的事务 ( <code>START TRANSACTION</code> ) <strong>，那么</strong>在他的这条新事务的线上，跟其他事务是没有联系的</strong>，也就是说，此时如果其他事务正在操作数据，它是不知道的。</p>
<p>然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">6</span>, <span class="hljs-string">&#x27;d&#x27;</span>, <span class="hljs-number">1000</span>);<br><span class="hljs-comment">-- ERROR 1062 (23000): Duplicate entry &#x27;6&#x27; for key &#x27;PRIMARY&#x27;</span><br></code></pre></td></tr></table></figure>

<p>报错了，操作被告知已存在主键为 <code>6</code> 的字段。这种现象也被称为<strong>幻读，一个事务提交的数据，不能被其他事务读取到</strong>。</p>
<h4 id="串行化"><a href="#串行化" class="headerlink" title="串行化"></a>串行化</h4><p>顾名思义，就是所有事务的<strong>写入操作</strong>全都是串行化的。什么意思？把隔离级别修改成 <strong>SERIALIZABLE</strong> :</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs mysql">SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;<br>SELECT @@GLOBAL.TRANSACTION_ISOLATION;<br>+--------------------------------+<br>| @@GLOBAL.TRANSACTION_ISOLATION |<br>+--------------------------------+<br>| SERIALIZABLE                   |<br>+--------------------------------+<br></code></pre></td></tr></table></figure>

<p>还是拿小张和小王来举例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><code class="hljs mysql">-- 小张 - 成都<br>START TRANSACTION;<br><br>-- 小王 - 北京<br>START TRANSACTION;<br><br>-- 开启事务之前先查询表，准备操作数据。<br>SELECT * FROM user;<br>+----+-----------+-------+<br>| id | name      | money |<br>+----+-----------+-------+<br>|  1 | a         |   900 |<br>|  2 | b         |  1100 |<br>|  3 | 小明      |  1000 |<br>|  4 | 淘宝店    |  1000 |<br>|  5 | c         |   100 |<br>|  6 | d         |  1000 |<br>+----+-----------+-------+<br><br>-- 发现没有 7 号王小花，于是插入一条数据：<br>INSERT INTO user VALUES (7, &#39;王小花&#39;, 1000);<br></code></pre></td></tr></table></figure>

<p>此时会发生什么呢？由于现在的隔离级别是 <strong>SERIALIZABLE ( 串行化 )</strong> ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照<strong>固定顺序执行</strong>，执行完一个事务后再继续执行下一个事务的<strong>写入操作</strong> ( <strong>这意味着队列中同时只能执行一个事务的写入操作</strong> ) 。</p>
<p>根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 <code>COMMIT</code> 结束它所处的事务，或者出现等待超时。</p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">Author: </span><span class="post-copyright-info"><a href="mailto:undefined">Li YunBO</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">Link: </span><span class="post-copyright-info"><a href="http://example.com/2021/04/17/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/">http://example.com/2021/04/17/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">Copyright Notice: </span><span class="post-copyright-info">All articles in this blog are licensed under <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">CC BY-NC-SA 4.0</a> unless stating additionally.</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/MySql/">MySql</a></div><div class="post_share"><div class="social-share" data-image="/img/%E5%A4%B4%E5%83%8F2.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2021/04/17/MySql%E7%B4%A2%E5%BC%95%E6%9C%AC%E8%B4%A8%E5%92%8CInnoDB/"><img class="prev-cover" src="/" onerror="onerror=null;src='/img/404.png'" alt="cover of previous post"><div class="pagination-info"><div class="label">Previous Post</div><div class="prev_info">索引本质和InnoDB</div></div></a></div><div class="next-post pull-right"><a href="/2021/04/17/Java%E4%B8%AD%E4%BC%9A%E5%AD%98%E5%9C%A8%E5%86%85%E5%AD%98%E6%B3%84%E9%9C%B2%E5%90%97/"><img class="next-cover" src="/" onerror="onerror=null;src='/img/404.png'" alt="cover of next post"><div class="pagination-info"><div class="label">Next Post</div><div class="next_info">Java中会存在内存泄露吗</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> Related Articles</span></div><div class="relatedPosts-list"><div><a href="/2021/04/19/MySQL/" title="MySQL"><img class="cover" src="/" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-19</div><div class="title">MySQL</div></div></a></div><div><a href="/2021/04/19/MySql_distributed/" title="mysql面试题"><img class="cover" src="/" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-19</div><div class="title">mysql面试题</div></div></a></div><div><a href="/2021/04/19/MySql_index/" title="mysql索引"><img class="cover" src="/" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-19</div><div class="title">mysql索引</div></div></a></div><div><a href="/2021/04/19/MySql_lock/" title="数据库锁"><img class="cover" src="/" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-19</div><div class="title">数据库锁</div></div></a></div><div><a href="/2021/04/19/MySql_optimization/" title="索引优化"><img class="cover" src="/" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-19</div><div class="title">索引优化</div></div></a></div><div><a href="/2021/04/19/MySql_skill/" title="MySql 小技巧"><img class="cover" src="/" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-19</div><div class="title">MySql 小技巧</div></div></a></div></div></div></div><div class="aside_content" id="aside_content"><div class="card-widget card-info"><div class="card-content"><div class="card-info-avatar is-center"><img class="avatar-img" src="/img/%E5%A4%B4%E5%83%8F2.jpg" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">Li YunBO</div><div class="author-info__description">给自己的警言：不要止步不前，不要因循守缺，不要纸上谈兵，不要不思进取，不要一直不要！</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">Articles</div><div class="length-num">193</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">Tags</div><div class="length-num">25</div></a></div><div class="card-info-data-item is-center"><a href="/categories/"><div class="headline">Categories</div><div class="length-num">18</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://gitee.com/Sobremesa_bolg"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://gitee.com/Sobremesa_bolg" target="_blank" title="Gitee"><i class="fab fa-github"></i></a></div></div></div><div class="card-widget card-announcement"><div class="card-content"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>Announcement</span></div><div class="announcement_content">这是我的博客</div></div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="card-content"><div class="item-headline"><i class="fas fa-stream"></i><span>Catalog</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0"><span class="toc-number">1.</span> <span class="toc-text">MySQL学习笔记</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%99%BB%E5%BD%95%E5%92%8C%E9%80%80%E5%87%BAMySQL%E6%9C%8D%E5%8A%A1%E5%99%A8"><span class="toc-number">1.1.</span> <span class="toc-text">登录和退出MySQL服务器</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%9F%BA%E6%9C%AC%E8%AF%AD%E6%B3%95"><span class="toc-number">1.2.</span> <span class="toc-text">基本语法</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#mysql%E5%BB%BA%E8%A1%A8%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.3.</span> <span class="toc-text">mysql建表约束</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1%E3%80%81%E4%B8%BB%E9%94%AE%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.3.1.</span> <span class="toc-text">1、主键约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3%E3%80%81%E5%94%AF%E4%B8%80%E4%B8%BB%E9%94%AE"><span class="toc-number">1.3.2.</span> <span class="toc-text">3、唯一主键</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5%E3%80%81%E9%BB%98%E8%AE%A4%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.3.3.</span> <span class="toc-text">5、默认约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.3.4.</span> <span class="toc-text">外键约束</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E4%B8%89%E5%A4%A7%E8%AE%BE%E8%AE%A1%E8%8C%83%E5%BC%8F"><span class="toc-number">1.4.</span> <span class="toc-text">数据库的三大设计范式</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1NF-%E7%AC%AC%E4%B8%80%E8%8C%83%E5%BC%8F"><span class="toc-number">1.4.1.</span> <span class="toc-text">1NF 第一范式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2NF-%E7%AC%AC%E4%BA%8C%E8%8C%83%E5%BC%8F"><span class="toc-number">1.4.2.</span> <span class="toc-text">2NF 第二范式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3NF"><span class="toc-number">1.4.3.</span> <span class="toc-text">3NF</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0"><span class="toc-number">1.5.</span> <span class="toc-text">查询练习</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%87%86%E5%A4%87%E6%95%B0%E6%8D%AE"><span class="toc-number">1.5.1.</span> <span class="toc-text">准备数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E5%88%B0-10"><span class="toc-number">1.5.2.</span> <span class="toc-text">1 到 10</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%86%E7%BB%84%E8%AE%A1%E7%AE%97%E5%B9%B3%E5%9D%87%E6%88%90%E7%BB%A9"><span class="toc-number">1.5.3.</span> <span class="toc-text">分组计算平均成绩</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%86%E7%BB%84%E6%9D%A1%E4%BB%B6%E4%B8%8E%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.4.</span> <span class="toc-text">分组条件与模糊查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2-1"><span class="toc-number">1.5.5.</span> <span class="toc-text">多表查询 - 1</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2-2"><span class="toc-number">1.5.6.</span> <span class="toc-text">多表查询 - 2</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%89%E8%A1%A8%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.7.</span> <span class="toc-text">三表关联查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2%E5%8A%A0%E5%88%86%E7%BB%84%E6%B1%82%E5%B9%B3%E5%9D%87%E5%88%86"><span class="toc-number">1.5.8.</span> <span class="toc-text">子查询加分组求平均分</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-1"><span class="toc-number">1.5.9.</span> <span class="toc-text">子查询 - 1</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-2"><span class="toc-number">1.5.10.</span> <span class="toc-text">子查询 - 2</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#YEAR-%E5%87%BD%E6%95%B0%E4%B8%8E%E5%B8%A6-IN-%E5%85%B3%E9%94%AE%E5%AD%97%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.11.</span> <span class="toc-text">YEAR 函数与带 IN 关键字查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E5%B1%82%E5%B5%8C%E5%A5%97%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.12.</span> <span class="toc-text">多层嵌套子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.13.</span> <span class="toc-text">多表查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-3"><span class="toc-number">1.5.14.</span> <span class="toc-text">子查询 - 3</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#UNION-%E5%92%8C-NOTIN-%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">1.5.15.</span> <span class="toc-text">UNION 和 NOTIN 的使用</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#ANY-%E8%A1%A8%E7%A4%BA%E8%87%B3%E5%B0%91%E4%B8%80%E4%B8%AA-DESC-%E9%99%8D%E5%BA%8F"><span class="toc-number">1.5.16.</span> <span class="toc-text">ANY 表示至少一个 - DESC ( 降序 )</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8%E7%A4%BA%E6%89%80%E6%9C%89%E7%9A%84-ALL"><span class="toc-number">1.5.17.</span> <span class="toc-text">表示所有的 ALL</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%8D%E5%88%B6%E8%A1%A8%E7%9A%84%E6%95%B0%E6%8D%AE%E4%BD%9C%E4%B8%BA%E6%9D%A1%E4%BB%B6%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.18.</span> <span class="toc-text">复制表的数据作为条件查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-4"><span class="toc-number">1.5.19.</span> <span class="toc-text">子查询 - 4</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9D%A1%E4%BB%B6%E5%8A%A0%E7%BB%84%E7%AD%9B%E9%80%89"><span class="toc-number">1.5.20.</span> <span class="toc-text">条件加组筛选</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#NOTLIKE-%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2%E5%8F%96%E5%8F%8D"><span class="toc-number">1.5.21.</span> <span class="toc-text">NOTLIKE 模糊查询取反</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#YEAR-%E4%B8%8E-NOW-%E5%87%BD%E6%95%B0"><span class="toc-number">1.5.22.</span> <span class="toc-text">YEAR 与 NOW 函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#MAX-%E4%B8%8E-MIN-%E5%87%BD%E6%95%B0"><span class="toc-number">1.5.23.</span> <span class="toc-text">MAX 与 MIN 函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E6%AE%B5%E6%8E%92%E5%BA%8F"><span class="toc-number">1.5.24.</span> <span class="toc-text">多段排序</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-5"><span class="toc-number">1.5.25.</span> <span class="toc-text">子查询 - 5</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#MAX-%E5%87%BD%E6%95%B0%E4%B8%8E%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.26.</span> <span class="toc-text">MAX 函数与子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-6"><span class="toc-number">1.5.27.</span> <span class="toc-text">子查询 - 6</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-7"><span class="toc-number">1.5.28.</span> <span class="toc-text">子查询 - 7</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2-8"><span class="toc-number">1.5.29.</span> <span class="toc-text">子查询 - 8</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%8C%89%E7%AD%89%E7%BA%A7%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.30.</span> <span class="toc-text">按等级查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.31.</span> <span class="toc-text">连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%86%85%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.5.31.1.</span> <span class="toc-text">内连接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%B7%A6%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.5.31.2.</span> <span class="toc-text">左外连接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%8F%B3%E5%A4%96%E9%93%BE%E6%8E%A5"><span class="toc-number">1.5.31.3.</span> <span class="toc-text">右外链接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%85%A8%E5%A4%96%E9%93%BE%E6%8E%A5"><span class="toc-number">1.5.31.4.</span> <span class="toc-text">全外链接</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1"><span class="toc-number">1.6.</span> <span class="toc-text">事务</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A6%82%E4%BD%95%E6%8E%A7%E5%88%B6%E4%BA%8B%E5%8A%A1-COMMIT-ROLLBACK"><span class="toc-number">1.6.1.</span> <span class="toc-text">如何控制事务 - COMMIT &#x2F; ROLLBACK</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%89%8B%E5%8A%A8%E5%BC%80%E5%90%AF%E4%BA%8B%E5%8A%A1-BEGIN-START-TRANSACTION"><span class="toc-number">1.6.2.</span> <span class="toc-text">手动开启事务 - BEGIN &#x2F; START TRANSACTION</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1%E7%9A%84-ACID-%E7%89%B9%E5%BE%81%E4%B8%8E%E4%BD%BF%E7%94%A8"><span class="toc-number">1.6.3.</span> <span class="toc-text">事务的 ACID 特征与使用</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1%E7%9A%84%E9%9A%94%E7%A6%BB%E6%80%A7"><span class="toc-number">1.6.4.</span> <span class="toc-text">事务的隔离性</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%84%8F%E8%AF%BB"><span class="toc-number">1.6.4.1.</span> <span class="toc-text">脏读</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%AF%BB%E5%8F%96%E5%B7%B2%E6%8F%90%E4%BA%A4"><span class="toc-number">1.6.4.2.</span> <span class="toc-text">读取已提交</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%B9%BB%E8%AF%BB"><span class="toc-number">1.6.4.3.</span> <span class="toc-text">幻读</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%B8%B2%E8%A1%8C%E5%8C%96"><span class="toc-number">1.6.4.4.</span> <span class="toc-text">串行化</span></a></li></ol></li></ol></li></ol></li></ol></div></div></div><div class="card-widget card-recent-post"><div class="card-content"><div class="item-headline"><i class="fas fa-history"></i><span>Recent Post</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/2021/08/16/%E6%B5%8B%E8%AF%95%E8%AE%B0%E5%BD%95/" title="测试"><img src="/img/%E5%92%8C%E6%9C%8D%E5%B0%91%E5%A5%B3.jpg%20img/%E8%9C%98%E8%9B%9B%E4%BE%A0.jpg" onerror="this.onerror=null;this.src='/img/404.png'" alt="测试"/></a><div class="content"><a class="title" href="/2021/08/16/%E6%B5%8B%E8%AF%95%E8%AE%B0%E5%BD%95/" title="测试">测试</a><time datetime="2021-08-16T01:20:46.666Z" title="Created 2021-08-16 09:20:46">2021-08-16</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/08/16/%E4%BA%8C%E5%8F%89%E6%A0%91%E7%9A%84%E9%81%8D%E5%8E%86/" title="二叉树的遍历"><img src="/img/%E5%92%8C%E6%9C%8D%E5%B0%91%E5%A5%B3.jpg%20img/%E8%9C%98%E8%9B%9B%E4%BE%A0.jpg" onerror="this.onerror=null;this.src='/img/404.png'" alt="二叉树的遍历"/></a><div class="content"><a class="title" href="/2021/08/16/%E4%BA%8C%E5%8F%89%E6%A0%91%E7%9A%84%E9%81%8D%E5%8E%86/" title="二叉树的遍历">二叉树的遍历</a><time datetime="2021-08-15T16:00:00.000Z" title="Created 2021-08-16 00:00:00">2021-08-16</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/08/10/%E8%AE%A4%E7%9C%9F%E7%9A%84+Netty+%E6%BA%90%E7%A0%81%E8%A7%A3%E6%9E%90%EF%BC%88%E4%BA%8C%EF%BC%89/" title="认真的 Netty 源码解析（二）"><img src="/img/%E5%92%8C%E6%9C%8D%E5%B0%91%E5%A5%B3.jpg%20img/%E8%9C%98%E8%9B%9B%E4%BE%A0.jpg" onerror="this.onerror=null;this.src='/img/404.png'" alt="认真的 Netty 源码解析（二）"/></a><div class="content"><a class="title" href="/2021/08/10/%E8%AE%A4%E7%9C%9F%E7%9A%84+Netty+%E6%BA%90%E7%A0%81%E8%A7%A3%E6%9E%90%EF%BC%88%E4%BA%8C%EF%BC%89/" title="认真的 Netty 源码解析（二）">认真的 Netty 源码解析（二）</a><time datetime="2021-08-10T07:53:27.000Z" title="Created 2021-08-10 15:53:27">2021-08-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/08/10/%E8%AE%A4%E7%9C%9F%E7%9A%84+Netty+%E6%BA%90%E7%A0%81%E8%A7%A3%E6%9E%90%EF%BC%88%E4%B8%80%EF%BC%89/" title="认真的 Netty 源码解析（一）"><img src="/img/%E5%92%8C%E6%9C%8D%E5%B0%91%E5%A5%B3.jpg%20img/%E8%9C%98%E8%9B%9B%E4%BE%A0.jpg" onerror="this.onerror=null;this.src='/img/404.png'" alt="认真的 Netty 源码解析（一）"/></a><div class="content"><a class="title" href="/2021/08/10/%E8%AE%A4%E7%9C%9F%E7%9A%84+Netty+%E6%BA%90%E7%A0%81%E8%A7%A3%E6%9E%90%EF%BC%88%E4%B8%80%EF%BC%89/" title="认真的 Netty 源码解析（一）">认真的 Netty 源码解析（一）</a><time datetime="2021-08-10T07:53:15.000Z" title="Created 2021-08-10 15:53:15">2021-08-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/08/10/Java+%E9%9D%9E%E9%98%BB%E5%A1%9E+IO+%E5%92%8C%E5%BC%82%E6%AD%A5+IO/" title="Java 非阻塞 IO 和异步 IO"><img src="/img/%E5%92%8C%E6%9C%8D%E5%B0%91%E5%A5%B3.jpg%20img/%E8%9C%98%E8%9B%9B%E4%BE%A0.jpg" onerror="this.onerror=null;this.src='/img/404.png'" alt="Java 非阻塞 IO 和异步 IO"/></a><div class="content"><a class="title" href="/2021/08/10/Java+%E9%9D%9E%E9%98%BB%E5%A1%9E+IO+%E5%92%8C%E5%BC%82%E6%AD%A5+IO/" title="Java 非阻塞 IO 和异步 IO">Java 非阻塞 IO 和异步 IO</a><time datetime="2021-08-10T07:52:59.000Z" title="Created 2021-08-10 15:52:59">2021-08-10</time></div></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By Li YunBO</div><div class="framework-info"><span>Framework </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>Theme </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="Read Mode"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="Switch Between Light And Dark Mode"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="Setting"><i class="fas fa-cog"></i></button><button class="close" id="mobile-toc-button" type="button" title="Table Of Contents"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="Back To Top"><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><div class="js-pjax"><script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div><script id="click-heart" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="false"></script></div><script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginModelPath":"assets/","model":{"jsonPath":"live2d-widget-model-koharu"},"display":{"position":"left","width":150,"height":300},"mobile":{"show":false},"rect":"opacity:0.7","log":false,"pluginJsPath":"lib/","pluginRootPath":"live2dw/","tagMode":false});</script></body></html>